VBA errors with Dim

J

jae113

Board Regular
Joined
Jun 17, 2008
Messages
227
Hi All,

I'm very new to VBA and was trying to combine a macro that I had recorded with a macro that someone gave me. Basically my hope was to have the last row and last column with data selected for both sorting and pivots. I keep getting errors even if I move the statements.

Right now it doesn't like the finalRow variable. I moved the
Set WSD = Worksheets("Data") & Set PTOutput = Worksheets("By SGD & Vendor")
​
down also, but I don't think that was right.


Any and all help very much appreciated!

Dim pt As PivotTable
Dim strField As String
Dim WSD As Worksheet
Dim PTOutput As Worksheet
Dim PTCache As PivotCache
Dim PRange As Range

Dim finalRow As Long
Set finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column

Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
TableName:="SamplePivot")

Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A[]"
.RightHeader = ""
.LeftFooter = "&F[]"
.CenterFooter = "&P of &N[]"
.RightFooter = "&D &T[]"
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Range("AF1").Select
ActiveCell.FormulaR1C1 = "Approver"
Range("A1").Select
Columns("AO:AO").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ActiveSheet.Select
ActiveSheet.Name = "Data"
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Data").Sort.SortFields.Add Key:=Range("finalCol:FinalRow"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Data").Sort
.SetRange Range("finalCol: FinalRow")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With
Range("AT1").Select
ActiveCell.FormulaR1C1 = "Image"
Range("AT2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""", HYPERLINK(RC[-1], ""IMAGE""))"
Range("AT2").Select
Selection.AutoFill Destination:=Range("FinalCol:FinalRow"), Type:=xlFillDefault
Range("FinalCol:FinalRow").Select

Cells.Select
Cells.EntireColumn.AutoFit
Set WSD = Worksheets("Data")

Set PTOutput = Worksheets("By SGD & Vendor")
pt.ManualUpdate = True

pt.AddFields RowFields:=Array( _
"SOURCING_GROUP_DESC", "VENDOR_NAME")

With pt.PivotFields("Amount")
.Orientation = xlDataField
.Function = xlSum
.Position = 1
.NumberFormat = "_($* #,##_);_($* (#,##0);_($* ""-""_);_(@_)"
End With
pt.ManualUpdate = False
Cells.Select
Cells.EntireColumn.AutoFit

End Sub
 
Sort by date Sort by votes
FinalRow varies for each worksheet, but FinalCol is AT. In the workbook I was just using, the finalRow was 59, but in another 362!

Thanks again Rorya
 
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Were those definitely the values when the code failed? Is the sheet protected?
 
Upvote 0
I just closed PERSONAL and got this error again:

Removed Records: Sorting from /xl/worksheets/sheet1.bin part
 
Upvote 0
The sheet is not protected. What do you mean about the values, I don't understand?

Here is the whole messy bit of code:


Sub Weekly_Report_Process()
'
' Weekly_Process Macro
' This will change the Import ID, Approver (AG) and Amount (AQ) columns, add the header and footer,
' sort descending by sum of amount and add the image link (AT)
With ActiveSheet.Select
ActiveSheet.Name = "Data"
End With
Dim WSD As Worksheet
Set WSD = Worksheets("Data")
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A[]"
.RightHeader = ""
.LeftFooter = "&F[]"
.CenterFooter = "&P of &N[]"
.RightFooter = "&D &T[]"
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Approver"
'Range("A1").Select
Columns("AQ:AQ").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ActiveSheet.Select
ActiveSheet.Name = "Data"
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select

With WSD.Sort.SortFields
.Clear
.Add Key:=WSD.Range("AQ1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With WSD.Sort
.SetRange WSD.Range("A1", WSD.Cells(finalRow, finalCol))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


With Range("AT1").Value = "Image"
Range("AT2:AT" & finalRow).FormulaR1C1 = "=IF(RC[-2]="""","""", HYPERLINK(RC[-1], ""IMAGE""))"
End With
Range("AT2:AT" & finalRow).Select
With Selection.Font
.Color = -65536
.TintAndShade = 0
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With

Cells.EntireColumn.AutoFit

End With
End Sub
 
Upvote 0
Got it!!!!!!!! It's working, although my PERSONAL keeps having to be repaired. It doesn't like the Sort and I don't know why. Here is the code... with corrections :)

Sub Weekly_Report_Process()
'
' Weekly_Process Macro
' This will change the Import ID, Approver (AG) and Amount (AQ) columns, add the header and footer,
' sort descending by sum of amount and add the image link (AT)
With ActiveSheet.Select
ActiveSheet.Name = "Data"
End With
Dim WSD As Worksheet
Set WSD = Worksheets("Data")
Dim finalRow As Long
finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
Dim finalCol As Long
finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A[]"
.RightHeader = ""
.LeftFooter = "&F[]"
.CenterFooter = "&P of &N[]"
.RightFooter = "&D &T[]"
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True
Columns("A:A").Select
Selection.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
Range("AG1").Select
ActiveCell.FormulaR1C1 = "Approver"
'Range("A1").Select
Columns("AQ:AQ").Select
Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
ActiveSheet.Select
ActiveSheet.Name = "Data"
Rows("1:1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select

With WSD.Sort.SortFields
.Clear
.Add Key:=WSD.Range("AQ1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With WSD.Sort
.SetRange WSD.Range("A1", WSD.Cells(finalRow, finalCol))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


With Range("AT1").Value = "Image"
Range("AT2:AT" & finalRow).FormulaR1C1 = "=IF(RC[-2]="""","""", HYPERLINK(RC[-1], ""IMAGE""))"
End With
Range("AT2:AT" & finalRow).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
With Selection.Font
.Color = -65536
.TintAndShade = 0
End With

Cells.EntireColumn.AutoFit

End With
End With
End Sub
 
Upvote 0
All that selecting is unnecessary. Try this:
Code: 
Sub Weekly_Report_Process()
'
' Weekly_Process Macro
' This will change the Import ID, Approver (AG) and Amount (AQ) columns, add the header and footer,
' sort descending by sum of amount and add the image link (AT)
   Dim WSD               As Worksheet
   Dim finalRow          As Long
   Dim finalCol          As Long

   Set WSD = ActiveSheet

   With Application
      .ScreenUpdating = False
      .PrintCommunication = False
   End With

   With WSD
      .Name = "Data"

      finalRow = .Cells(.Rows.Count, 1).End(xlUp).Row
      finalCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

      With .PageSetup
         .PrintArea = ""
         .PrintTitleRows = ""
         .PrintTitleColumns = ""
         .LeftHeader = ""
         .CenterHeader = "&A[]"
         .RightHeader = ""
         .LeftFooter = "&F[]"
         .CenterFooter = "&P of &N[]"
         .RightFooter = "&D &T[]"
         .CenterHorizontally = False
         .CenterVertically = False
         .Orientation = xlLandscape
         .Draft = False
         .PaperSize = xlPaperLetter
         .FirstPageNumber = xlAutomatic
         .Order = xlDownThenOver
         .BlackAndWhite = False
         .Zoom = False
         .FitToPagesWide = 1
         .FitToPagesTall = False
      End With

      Application.PrintCommunication = True
      .Columns("A:A").NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@"
      .Range("AG1").Value = "Approver"
      'Range("A1").Select
      .Columns("AQ:AQ").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
      With .Rows("1:1")
         With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
         End With
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlBottom
         .WrapText = False
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = xlContext
         .MergeCells = False
      End With

      With .Sort
         With .SortFields
            .Clear
            .Add Key:=WSD.Range("AQ1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
         End With
         .SetRange WSD.Range("A1", WSD.Cells(finalRow, finalCol))
         .Header = xlYes
         .MatchCase = False
         .Orientation = xlTopToBottom
         .SortMethod = xlPinYin
         .Apply
      End With

      .Range("AT1").Value = "Image"
      
      With .Range("AT2:AT" & finalRow)
         .FormulaR1C1 = "=IF(RC[-2]="""","""", HYPERLINK(RC[-1], ""IMAGE""))"
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlCenter
         With .Font
            .Color = -65536
            .TintAndShade = 0
         End With
      End With
      
      .Cells.EntireColumn.AutoFit

   End With
End Sub
 
Upvote 0
Again - YOU ARE AWESOME!!!!!!!!!!!!!


Thank you so much! I see how I kept selecting and ending. I thought I had to since that was the way it recorded.

Thank you so much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macro to Autofit Cols Page Preview
Replies
6
Views
215
Logit
Logit
R
  • Question Question
Runtime Error 91
2
Replies
15
Views
709
rtr1811
R
J
  • Solved
VBA/Macro: Adding Pivot table with connection string
Replies
1
Views
400
Jette3173
J
F
  • Question Question
Printing: File settings override code
Replies
1
Views
125
Flavien
F
I
  • Question Question
Error "Unable to set the Name property of the PivotTable class"
Replies
1
Views
333
RoryA
RoryA

Forum statistics

Threads
1,226,217
Messages
6,189,687
Members
453,563
Latest member
Aswathimsanil

Share this page

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
Back
Top