0

Sorry for what is possibly a very elementary question, but I'm a self-taught beginner!

I'm trying to loop through my workbook and copy a range from each sheet into a new workbook with the name of the tab appended.

The issue is that the range varies from worksheet to worksheet. It always begins at A14 but can have any number of rows, so I tried to set my range to Column I and the Row to the Match position of "Total", less 1.

Logically, I felt pretty good, but every time I put "ws." in front of my initial Range query I get "Method 'Range of object ' _Worksheet' failed". And if I take "ws." away, then my range that copies over stays at the Active workbook.

Any help here would be tremendously appreciated!

Sub forEachECWS()

Dim ws As Worksheet
Dim myRangeL1 As Range
Dim PasteRange As Range

    For Each ws In ActiveWorkbook.Worksheets
        'Unmerge All Cells
        Cells.UnMerge
        
        'Select Loader 1 Range
        Set myRangeL1 = ws.Range(Cells(14, "A"), Cells(Application.Match("Total", ws.Range("A:A"), 0) - 1, "I"))
        
        'Copy Loader 1 and paste into Output Workbook, Loader 1 Tab
        myRangeL1.Copy
        Workbooks("Output Workbook.xlsx").Worksheets("Loader 1").Range("A" & Rows.Count).End(xlUp).Offset(2, 1).PasteSpecial xlPasteValues
        Application.CutCopyMode = False
        
        Workbooks("Output Workbook.xlsx").Worksheets("Loader 1").Activate
        Set PasteRange = Range(Range("A" & Rows.Count).End(xlUp).Offset(2, 0), Cells(Range("F" & Rows.Count).End(xlUp).Row, "A"))
        
        Workbooks("Origin Workbook").Activate
        PasteRange = ws.Name
    Next
    

End Sub
Junkyard84
  • 1
  • 2
    `Cells.UnMerge` --> `ws.Cells.UnMerge`. Then `Set myRangeL1 = ws.Range(ws.Cells(14, "A", ws.Cells( ... ))`. Best practice in VBA is to qualify every `Range` and `Cells` and `Rows` and `Columns` calls with the parent worksheet/workbook. Btw, it's much better to move the `Application.Match` into its own line, assigning the result to a variable and then testing if that variable is an error using `IsError`. Otherwise, your `Set myRangeL1` will blow up if no match is found. – BigBen Jul 31 '23 at 14:09
  • Thank you! I didn't know I had to go to that extent of qualification. Extremely helpful for now and future knowledge. – Junkyard84 Jul 31 '23 at 14:18
  • 1
    That level of qualification is highly encouraged, otherwise you run into problems with an implicit `ActiveSheet` / `ActiveWorkbook`. Implicitly you have `ws.Range(ActiveSheet.Cells(14, "A")...`, which fails when `ws` is not the same as `ActiveSheet`. – BigBen Jul 31 '23 at 14:19

0 Answers0