VBA Script to automatically copy & paste data from multiple worksheets to a master sheet

[steve2115]

Looking for a vba script to automatically copy & paste data from multiple worksheets to a master sheet named Returns. The data that I need to copy resides in columns A through N on multiples sheets. These sheet names are Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec. However I do not want to copy the header row for each so Row1 on each of these sheets should be excluded. Also, I want to exclude all row data that has "GR for acc.assgt rev"
in column C of all sheets.


Any help would be appreciated.

Thanks in Advance

 

[steve2115]

Just ran the code...Good news for row 2..it copied and pasted it this time..
Bad news is when the data is pasted to "Return" it's showing blank rows between the data points.

Jan sheet has 27 rows - (pasted to rows 2 through 28 on Return sheet)
(rows 29 through 859 are blank)
Feb sheet has 27 rows - (pasted to row 860 through 886 on Return sheet)

Note: all of the other sheets (Mar-Dec) are completely blank on this test template.

 

[brian.wethington]

I am testing it with blank sheets on Mar - Dec as well so I am getting header rows showing.

In tests the only way I get the results of not deleting rows are if a row only appears to be blank, but does have a space in it. If you go to the Jan sheet and select cell A2 and press Ctrl+[down arrow] does it go to row 28 or row 859? If there is a hidden character in a cell then it is still filled and as such we would have to approach it differently.

 

[steve2115]

With a bit of tweaking I got it to work. Thanks for all your help on this. This will save several people from pulling their hair out. I added a secondary filter to get rid of the header rows that were comiong over onto the Returns sheet. I was able to modify the formula in column A on the Jan-Dec sheets so that the code would handle it cleaner.


Here is the code that I ended up with.

Sub DetailConsolidation()
Dim WS As Worksheet, WS1 As Worksheet, WB As Workbook
Dim MyArray, x As Long
MyArray = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
Set WS1 = ActiveWorkbook.Worksheets("Returns")
For x = 0 To 11
Set WS = ActiveWorkbook.Worksheets(MyArray(x))
WS.Range("A1:N" & WS.Cells(Rows.Count, 1).End(xlUp).Row).Copy WS1.Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next
If WorksheetFunction.CountIf(WS1.Range("C:C"), "GR for acc.assgt rev") > 0 Then
WS1.Range("A2", WS1.Cells(Rows.Count, "n").End(xlUp)).AutoFilter Field:=3, Criteria1:="GR for acc.assgt rev"
WS1.Range("A2", WS1.Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WS1.Range("A2").AutoFilter
End If

If WorksheetFunction.CountIf(WS1.Range("C:C"), "Vendor Material Number") > 0 Then
WS1.Range("A2", WS1.Cells(Rows.Count, "n").End(xlUp)).AutoFilter Field:=3, Criteria1:="Vendor Material Number"
WS1.Range("A2", WS1.Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WS1.Range("A2").AutoFilter
End If
If WorksheetFunction.CountBlank(WS1.Range("A1", WS1.Cells(Rows.Count, 2).End(xlUp))) > 0 Then
WS1.Range("A1", WS1.Cells(Rows.Count, 2).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If
End Sub