Pulling Information into new workbook from dynamic Worksheet Name

Question

VBA issue coming up.

I have a revenue tracker ("revenue tracker.xlsx") file in which I have created a macro that filters a particular month entries and creates a new worksheets based on the months entries (worksheets are entitled "January", "February" etc)

I would now like to pull this newly generated worksheet over to a new workbook ("Cost Loader.xlsx"), considering the worksheet name is dynamic.

my code is as follows

Sub PullRevenueTrackerInfo()

'Pull info from respective column into correct column on to Cost Loader

Dim ws_mth As Workbook, ws_charges As Workbook, mapFromColumn As Variant, mapToColumn As Variant
    Dim lastCell As Integer, i As Integer, nextCell As Integer, arrCopy As Variant

Dim tbl As ListObject
Dim wsNames As Variant, ws As Worksheet, w As Worksheet, El As Variant, boolFound As Boolean


'2. Dynamic worksheets

wsNames = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")
    For Each w In Worksheets
        For Each El In wsNames
    If w.Name = El Then
        Set ws = w: boolFound = True: Exit For
    End If
    Next El
    Next w


'3. pull information

     Set ws_mth = Workbooks("Revenue Tracker.xlsx")
    Set ws_charges = Workbooks("Cost Loader.xlsx")
    
    mapFromColumn = Array("I", "J", "K", "L", "M", "N", "O", "P")  after
    mapToColumn = Array("A", "B", "C", "G", "K", "M", "H", "J") 

  For i = 0 To UBound(mapFromColumn)
  With ws_mth.Worksheets(ws.Name)   'run time 91 error - object variable not set
            lastCell = ws_mth.Sheets(ws.Name).ListObjects("Table_owssvr").Range.Rows.Count
            arrCopy = .Range(mapFromColumn(i) & 2 & ":" & mapFromColumn(i) & lastCell)
        End With

            With ws_charges.Worksheets(1)
                nextCell = .Range(mapToColumn(i) & .Rows.Count).End(xlUp).Row + 1
                .Range(mapToColumn(i) & nextCell).Resize(UBound(arrCopy), UBound(arrCopy, 2)).Value = arrCopy
            End With
            Next i

I am getting a type mismatch error arising when trying to map from particular columns in which I have data. Ive tried a couple of different things, its pretty frustrating at the moment, I cant seem to get it to work. thanks in advance!

EDIT: run time error appears `With ws_mth.Worksheets(ws.Name), im not sure how to call the correct worksheet

Answer 1

I've reformated your code a bit, edited it, commented and I think your problem is solved:

Sub PullRevenueTrackerInfo()

'Pull info from respective column into correct column on to Cost Loader
    
    'Try to "group" variables like so, it helps you and others to catch-up while debugging
    Dim wb_mth, wb_charges As Workbook
    Dim mapFromColumn, mapToColumn As Variant
    Dim lastCell, i, nextCell As Integer
    Dim arrCopy, wsNames As Variant

    Dim tbl As ListObject
    Dim ws, w As Worksheet
    Dim El As Variant
    Dim boolFound As Boolean

    'Pay attention to variable naming!
    'You named those as "ws_mth" and "ws_charges"
    'which indicates you want Worksheets, but you want Workbooks
    Set wb_mth = Workbooks("Revenue Tracker.xlsx")
    Set wb_charges = Workbooks("Cost Loader.xlsx")

'2. Dynamic worksheets

wsNames = Split("January,February,March,April,May,June,July,August,September,October,November,December", ",")
    
    For Each w In wb_mth.Worksheets
        For Each El In wsNames
            If w.Name = El Then
                'Again, pay attention. You shall specify in which workbook your "ws" is
                Set ws = w: boolFound = True: Exit For
            End If
        Next El
        If boolFound Then Exit For 'Add this to exit second loop
    Next w


'3. pull information
    
    mapFromColumn = Array("I", "J", "K", "L", "M", "N", "O", "P")
    mapToColumn = Array("A", "B", "C", "G", "K", "M", "H", "J")

    For i = 0 To UBound(mapFromColumn)
        'Main problem. You tried to reference to worksheet by name,
        'while your target worksheet, as an object is already set (ws), so you can do:
        
        'With ws_mth.Worksheets(ws.Name)   'run time 91 error - object variable not set
        With ws
            lastCell = .ListObjects("Table_owssvr").Range.Rows.Count
            arrCopy = .Range(mapFromColumn(i) & 2 & ":" & mapFromColumn(i) & lastCell)
        End With

        With wb_charges.Worksheets(1)
            nextCell = .Range(mapToColumn(i) & .Rows.Count).End(xlUp).Row + 1
            .Range(mapToColumn(i) & nextCell).Resize(UBound(arrCopy), UBound(arrCopy, 2)).Value = arrCopy
        End With
    Next i

End Sub

I assume that you expect such output (here I ran macro twice):

Please let me know, if it works for you :)