VBA Variable in Cells reference

Brock_Hardchest

New Member
Joined
Feb 23, 2018
Messages
23
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?

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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Threads
1,127,294
Messages
5,623,824
Members
415,991
Latest member
RicardoSS

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