Offsetting a PasteValuesAndNumberFormats with PasteFormats

Status
Not open for further replies.

Jamuson

New Member
Joined
Dec 18, 2020
Messages
4
Platform
  1. MacOS
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.

VBA Code:
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!
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,551
Office Version
  1. 365
Platform
  1. Windows
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.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,127,190
Messages
5,623,284
Members
415,962
Latest member
Blu3berry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top