Brock_Hardchest
New Member
- Joined
- Feb 23, 2018
- Messages
- 27
Hello,
I am having an issue copying the cells of a pivot table. All the variable are defined as strings. I think row2 and lastcolumn are messed up somehow.
Testing other things in place of row2, and lastcolumn seem to work ok.
.Range(Cells(row1, 1), Cells(row2, 7)) works ok
.Range(Cells(row1, 1), Cells(row2, row1)) works ok. row1 is assigned earlier in the macro
.Range(Cells(row1, 1), Cells(row2, lastcolumn)) errors
.Range(Cells(row1, 1), Cells(row2, row2)) errors
I have tried adding a . in front of Cells but that caused a different error. Error produced is 1004 Application-defined or object-defined error. Any ideas?
I am having an issue copying the cells of a pivot table. All the variable are defined as strings. I think row2 and lastcolumn are messed up somehow.
Testing other things in place of row2, and lastcolumn seem to work ok.
.Range(Cells(row1, 1), Cells(row2, 7)) works ok
.Range(Cells(row1, 1), Cells(row2, row1)) works ok. row1 is assigned earlier in the macro
.Range(Cells(row1, 1), Cells(row2, lastcolumn)) errors
.Range(Cells(row1, 1), Cells(row2, row2)) errors
I have tried adding a . in front of Cells but that caused a different error. Error produced is 1004 Application-defined or object-defined error. Any ideas?
VBA Code:
Sheets("Customer Pivot").Activate
ActiveSheet.PivotTables("PivotTable1").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields("Date").PivotFilters.Add _
Type:=xlAfter, Value1:=monthend5
lastcolumn = Cells(4, Columns.Count).End(xlToLeft).Column
row2 = Cells(Rows.Count, 1).End(xlUp).row
'paste customer pivot to Abs Variance tab and filter
Sheets("Abs Variance").Activate
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
End If
Sheets("Abs Variance").Cells.ClearContents
Sheets("Abs Variance").Cells.ClearFormats
CheckerWB.Sheets("Customer Pivot").Activate
CheckerWB.Sheets("Customer Pivot").Range(Cells(row1, 1), Cells(row2, lastcolumn)).SpecialCells(xlCellTypeVisible).Copy
CheckerWB.Sheets("Abs Variance").Cells(1, 1).PasteSpecial xlValues