K
klysell
I tried to use some code given to me on this site to delete rows after
macro-generated pivot tables, but this did not work. I received an error
message, "Method 'Range' of Object '_Worksheet' failed". The code that I
received was as follows:
Dim myR As Range
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Set myR = Selection
Range(myR(myR.Cells.Count)(2), Cells(Rows.Count, 1)).EntireRow.Delete
Range(myR(myR.Cells.Count)(1, 2), Cells(1, Columns.Count)).EntireColumn.Delete
'
'
'
This code sandwiched in with other code as shown below:
Sheets("PIV_SOF").Visible = True
Application.Goto "SOF_BACK_TO_SUMMARY"
Worksheets("PIV_SOF").Copy _
After:=Worksheets(Worksheets.Count)
Sheets("PIV_SOF").Visible = False
Set Sh = ActiveSheet
Sh.Name = Target & "-Source of Funds"
Sh.Tab.ColorIndex = 33
For Each pt In Sh.PivotTables
With pt
With .PivotFields("RC")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
..CurrentPage = pi.Value
Dim myR As Range
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel,
True
Set myR = Selection
Range(myR(myR.Cells.Count)(2), Cells(Rows.Count, 1)).EntireRow.Delete
Range(myR(myR.Cells.Count)(1, 2), Cells(1,
Columns.Count)).EntireColumn.Delete
Call Formatting_SOF
'
'
'
'
Any suggestions?
Thanks in advance.
macro-generated pivot tables, but this did not work. I received an error
message, "Method 'Range' of Object '_Worksheet' failed". The code that I
received was as follows:
Dim myR As Range
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
Set myR = Selection
Range(myR(myR.Cells.Count)(2), Cells(Rows.Count, 1)).EntireRow.Delete
Range(myR(myR.Cells.Count)(1, 2), Cells(1, Columns.Count)).EntireColumn.Delete
'
'
'
This code sandwiched in with other code as shown below:
Sheets("PIV_SOF").Visible = True
Application.Goto "SOF_BACK_TO_SUMMARY"
Worksheets("PIV_SOF").Copy _
After:=Worksheets(Worksheets.Count)
Sheets("PIV_SOF").Visible = False
Set Sh = ActiveSheet
Sh.Name = Target & "-Source of Funds"
Sh.Tab.ColorIndex = 33
For Each pt In Sh.PivotTables
With pt
With .PivotFields("RC")
For Each pi In .PivotItems
If LCase(pi.Value) = LCase(Target.Value) Then
..CurrentPage = pi.Value
Dim myR As Range
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel,
True
Set myR = Selection
Range(myR(myR.Cells.Count)(2), Cells(Rows.Count, 1)).EntireRow.Delete
Range(myR(myR.Cells.Count)(1, 2), Cells(1,
Columns.Count)).EntireColumn.Delete
Call Formatting_SOF
'
'
'
'
Any suggestions?
Thanks in advance.