VBA - Copy Paste rows (including Blanks) + Offset Question

[xceln00b]

Frustrated with this problem...

I have a workbook with multiple sheets. I want to take the value of Cell A2 from every sheet (including Blank cells), and Paste it into Cell A2 of this "Master" sheet and offset the row by 1 and continue pasting row after row.

Sub CopyIt()
Dim ws As Worksheet
Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            ws.Range("A2").Copy
            Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1,0).PasteSpecial xlPasteValues
        End If
    Next
Application.ScreenUpdating = True
End Sub

The problem I have is that the above code ignores blank cells in the sheets. I think the problem is the .End(xlUp) property. But how do I modify the code so that it pastes the value starting in Cell A2 (including blanks) and offsets by 1 row down?

 

[JoeMo]

Try:

Sub CopyIt()
Dim ws As Worksheet, Ct As Long
Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            Ct = Ct + 1
            ws.Range("A2").Copy
            Sheets("Master").Range("A1").Offset(Ct).PasteSpecial xlPasteValues
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[xceln00b]

Wow. I feel silly for having researched this for hours. Thank you so much!

You made my day!

 

[JoeMo]

Wow. I feel silly for having researched this for hours. Thank you so much!

You made my day!

You are welcome - thanks for the reply.

 

[xceln00b]

For anyone looking for the whole code:
First, the program adds a new sheet called "Master", then it scans all the sheets from the workbook and copies the Cell A2 (regardless of whether the cell is blank or not) from every sheet that's not "Master" and pastes it into Cell A2, A3, A4... of the Master sheet and so on.

Sub CopyIt()
 
Dim ws As Worksheet, x As Long
 
Application.ScreenUpdating = False
 
Worksheets.Add.Name = "Master"
Sheets("Master").Range("A1").Value = "Associate Name"
 
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Master" Then
            x = x + 1
            ws.Range("A2").Copy
            Sheets("Master").Range("A1").Offset(x).PasteSpecial xlPasteValues
           
        End If
    Next
 
Columns.AutoFit
 
Application.ScreenUpdating = True
 
End Sub