Offsetting a PasteValuesAndNumberFormats with PasteFormats

[Jamuson]

I have a macro that works for the first instance of pasting into the new sheet. The problem is that when I offset the second sheet, I can't get both the ValuesAndNumberFormat and Format to "line up". Heres what I have...Im going to repeat the operation on 8 sheets total, to combine them into one with the original formatting...Im sure there is a way to Loop it to run through all the sheets and never paste over data, plus keep all the formatting.

Sub Data_Scrubber()

Dim count_col, count_row As Integer
Dim orig, output As Worksheet

Worksheets("Sheet1").Activate

Set orig = ThisWorkbook.Sheets("Sheet1")
Set output = ThisWorkbook.Sheets("Corrections")

count_col = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlDown)))

ActiveSheet.Range("A2").AutoFilter Field:=1, Criteria1:="<>"

orig.Range(Cells(2, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
output.Cells(2, 1).PasteSpecial xlPasteValuesAndNumberFormats
output.Cells(2, 1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False

Worksheets("Sheet2").Activate

Set orig = ThisWorkbook.Sheets("Sheet2")
Set output = ThisWorkbook.Sheets("Corrections")

count_col = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlDown)))

ActiveSheet.Range("A2").AutoFilter Field:=1, Criteria1:="<>"

orig.Range(Cells(2, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
output.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
output.[COLOR=rgb(209, 72, 65)]Cells(2, 1).[/COLOR]PasteSpecial xlPasteFormats
Application.CutCopyMode = False



End Sub

Any help would be appreciated!

 

[Joe4]

Duplicate to: Correction to original

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

I am closing this original one, and leaving your latest one.