Change excel sheet reference from numerical sequence to sheet name

Question

I have a relatively simple question. Currently I have some code that is working well but not efficient. I have about 500 cost centres each with their own workbook which I have consolidated into a central repository (reference - Wb2 in code below). The code copies ranges from each open template (Wb1) into my consolidation (Wb2). Sample code below (full code not necessary).

Option Explicit

Sub CopyData()
    Dim Wb1 As Workbook, wb2 As Workbook, wB As Workbook
    Dim rngToCopy1 As Range
    Dim rngToCopy2 As Range
    Dim rngToCopy3 As Range
    Dim rngToCopy4 As Range
    Dim rngToCopy5 As Range
    Set wb2 = ThisWorkbook
    Application.Calculation = xlManual

    For Each wB In Application.Workbooks
        If Not Left(wB.Name, 18) = "Consolidation Test" Then
            Set Wb1 = wB
            Exit For
        End If
    Next

    'Forecast Data
    With Wb1.Sheets(1)
        Set rngToCopy1 = .Range("A11:O11", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    wb2.Sheets(7).Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(rngToCopy1.Rows.Count, 15).Value = rngToCopy1.Value
    wb2.Sheets(7).Range("P" & Rows.Count).End(xlUp).Offset(1).Resize(rngToCopy1.Rows.Count).Value = Sheets(3).Range("J1").Value

I have tried to simply change as an example wB2.Sheets(7) to Wb2.Sheets("Forecast_Data") as well as the original name in VBE wB2.Sheets("Sheet4") but I get a subscript out of range error?

In addition to the solution, please also provide background on why the fix is not so simple, has it got to do with how my variables are declared?

Also, with the copying happening outside of the for loop, only data from **one** workbook will get copied over..... — Mistella, Jan 16 '19 at 13:40
`Wb2.Sheets("Forecast_Data")` is correct, and so is `wB2.Sheets("Sheet4")`. If you get a subscript error, `wb2` does not have such sheets. — GSerg, Jan 16 '19 at 13:40
Also, if you have ever saved macros in the PERSONAL workbook (the default save location), that could be causing this issue because once it exists, the PERSONAL workbook is always open in the background while Excel is running. If your code is cycling through all workbooks, that would include PERSONAL. — Mistella, Jan 16 '19 at 13:53

score 0 · Answer 1 · answered Jan 16 '19 at 13:53

0

I think @GSerg already gave you the most important hint.

I assume you created a workbook with the name WB2, but it does not contain any of those sheets you want to write in.

Either create them manually in your template (WB2) or have a look at How to Add a Named Sheet at the end of all excel sheets to create them with your VBA.

answered Jan 16 '19 at 13:53

David Weber

86
5

So basically ```wb2``` is just a variable, I have ```set wb2``` at the beginning of the code = this workbook. This helps because each template I need to copy over has a different name. I sort of understand what you and @ GSerg are saying, that there is no such worksheet as ```wb2.Sheets("Forecast_data")``` because my workbook is named "consolidation"? If this is the case why does ```wb2.Sheets(7)``` work without any problems? – M Breganski Jan 16 '19 at 14:30
@MBreganski It does not matter what your workbook is named. Does the file that contains the `CopyData` macro has a sheet named `Forecast_data`? – GSerg Jan 16 '19 at 16:44
Yeah it does - weird thing is I can run separate subs with those reference names without any issues (reference then excludes the ```wb2``` bit). So I thought it may have to do with the part I have ```set wb2 = ThisWorkbook``` ? Do I need to be consistent or should the referencing work interchangeably between numerical sheet sequence / sheet names? Functionally I can manage by referencing the sheets numerically but I'd really like to know the reason behind the issue... – M Breganski Jan 17 '19 at 14:53

Change excel sheet reference from numerical sequence to sheet name

1 Answers1