Macro to copy first two columns from all sheets to a master sheet is skipping sheets

Question

I'm using this macro to copy columns A and B from all of my sheets into a new sheet named Master. What I notice is that entire sheets worth of information are missing in the master sheet and I can't figure out why. The format for my sheets is column A has a string of characters that follow this structure: M2,004,005,004,007,17,096,01:07:45,45 and column B is just a date such as 4/19/2017.

I have hundreds of these sheets in my workbook and each has 224 rows that I need to copy into a single master sheet. Could anyone help me figure out how to get this code to stop skipping sheets?

Thanks.

Sub CreateMaster()

Dim J As Integer

On Error Resume Next

Sheets(1).Select

Worksheets.Add

Sheets(1).Name = "Master"

Sheets(2).Activate

Range("A1:B1").EntireRow.Select

Selection.Copy Destination:=Sheets(1).Range("A1:B1")

For J = 2 To Sheets.Count

Sheets(J).Activate

Range("A1:B1").Select

Selection.CurrentRegion.Select

Selection.Copy Destination:=Sheets(1).Range("A65536:B65536").End(xlUp)(2)

Next

End Sub

while searching for solutions online, I came across this macro that seems to do the same thing, but also seems to skip the exact same sheets as my macro does.

Sub CopyFromWorksheets()

Dim wrk As Workbook 'Workbook object - Always good to work with object

variables

Dim sht As Worksheet 'Object for handling worksheets in loop

Dim trg As Worksheet 'Master Worksheet

Dim rng As Range 'Range object

Dim colCount As Integer 'Column count in tables in the worksheets

Set wrk = ActiveWorkbook 'Working in active workbook

For Each sht In wrk.Worksheets 
    If sht.Name = "Master" Then 
        MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _ 
        "Please remove or rename this worksheet since 'Master' would be" & _ 
        "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error" 
        Exit Sub 
    End If 
Next sht 

 'We don't want screen updating
Application.ScreenUpdating = False 

 'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 
 'Rename the new worksheet
trg.Name = "Master" 
 'Get column headers from the first worksheet
 'Column count first
Set sht = wrk.Worksheets(1) 
colCount = sht.Cells(1, 255).End(xlToLeft).Column 
 'Now retrieve headers, no copy&paste needed
With trg.Cells(1, 1).Resize(1, colCount) 
    .Value = sht.Cells(1, 1).Resize(1, colCount).Value 
     'Set font as bold
    .Font.Bold = True 
End With 

 'We can start loop
For Each sht In wrk.Worksheets 
     'If worksheet in loop is the last one, stop execution (it is Master worksheet)
    If sht.Index = wrk.Worksheets.Count Then 
        Exit For 
    End If 
     'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
    Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 
     'Put data into the Master worksheet
    trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value 
Next sht 
 'Fit the columns in Master worksheet
trg.Columns.AutoFit 

 'Screen updating should be activated
Application.ScreenUpdating = True

End Sub

as a workaround, since only the most recent data is immediately pertinent, I worked around it, but deleting the first 150 sheets. that still left around 100 sheets for my macro to work on, but now the missing pieces of data seem to be there. I wonder if there's something about the quantity of sheets that is causing this to malfunction?

Does it still skip them if you remove the On Error Resume Next? — Steve Lovell
Yes. I just tried that and it continues to skip the same sheets. — David Toscano
When you do the for each in, it doesn't necessarily go in numerical order. Try changing your loop to do something like this Dim thisSht as Worksheet For x = 1 to wrk.Worksheet.Count set thisSht = wrk.worksheets(x) — Liss
Ok. Anything odd about those sheets? Eg the data in A1:B1 or neighbouring cells? Could they be hidden rows or protected sheets? I'd be inclined to make one of those sheets the only sheet in the book and see what happens then. — Steve Lovell
there's nothing special on any of the sheets. It's just data that's been collected over a year now. Liss helped me figure things out though. Thanks your help. — David Toscano

Liss Liss · Accepted Answer · 2017-04-21T21:00:54

Comments may not get it across correctly. Restructure your loop (and add the variables mentioned).

Dim x as Long
Dim thisSht as Worksheet

For x = 1 to wrk.Worksheets.Count
    set thisSht = wrk.Worksheets(x)
     'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets
    Set rng = thisSht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 
     'Put data into the Master worksheet
    trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value 
Next x

Macro to copy first two columns from all sheets to a master sheet is skipping sheets

1 Answers