Expand macro

The attached sheet and macro code creates output tabs as if both input tabs (pm and rm) were always going to have the names pm and rm.   I am wanting the macro to run against whatever tab is currently active only, regardless of the name.  In the output tab, I also need two (2) new empty columns (between C&D) named LegType, and Leg#, respectively.  Assistance is greatly appreciated.
Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim vSheets() As Variant
  Dim vSheetname As Variant
  Dim rngSrc As Range
  vSheets = Array("pm", "rm")
  On Error Resume Next
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  For Each vSheetname In vSheets
    Set wks = ActiveWorkbook.Worksheets(vSheetname)
    Set rngSrc = wks.Cells.Find("Account Trade History")
    Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
    ActiveWorkbook.Worksheets(vSheetname & "_output").Delete
    Set wks = ActiveWorkbook.Worksheets.Add
    wks.Name = vSheetname & "_output"
    rngSrc.Copy wks.Range("A1")
  Next
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

trade-macro-v2.1d.xlsm
rtod2Asked:
Who is Participating?
 
aikimarkConnect With a Mentor Commented:
this should work
Option Explicit

Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim rngSrc As Range
  Dim strSheetname As String
  
  On Error Resume Next
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  Set wks = ActiveSheet
  strSheetname = wks.Name
  Set rngSrc = wks.Cells.Find("Account Trade History")
  Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
  ActiveWorkbook.Worksheets(strSheetname & "_output").Delete
  Set wks = ActiveWorkbook.Worksheets.Add
  wks.Name = strSheetname & "_output"
  rngSrc.Copy wks.Range("A1")
  
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

0
 
rtod2Author Commented:
0
All Courses

From novice to tech pro — start learning today.