cwmoore_2001
New Member
- Joined
- Dec 5, 2017
- Messages
- 3
Hello. I am running Office 365 on a Windows laptop. I am trying to automate a report I run multiple times a month.
I have recorded a macro and have set a variable called lr. I just don't know the exact text after SourceData so that my range will be dynamic.
Thoughts?
==here is my code==
' create a variable
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
'create pivot table
Sheets("Open Dena Orders").Select
Sheets("Open Dena Orders").Name = "Detail"
Range("C7").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Detail!R1C1:R132C12", Version:=7).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=7
I have recorded a macro and have set a variable called lr. I just don't know the exact text after SourceData so that my range will be dynamic.
Thoughts?
==here is my code==
' create a variable
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
'create pivot table
Sheets("Open Dena Orders").Select
Sheets("Open Dena Orders").Name = "Detail"
Range("C7").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Detail!R1C1:R132C12", Version:=7).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable1", DefaultVersion:=7