VBA errors with Dim

[jae113]

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

 

[jae113]

Thanks! I'm going to ask you to explain it in a minute, if you don't mind , but I'm getting an error on this line:

With .Sort


Runtime Error 438

Object doesn't support this property or method

 

[RoryA]

Sorry, that should be:

With WSD.Sort

 

[jae113]

I'm using Excel 2010 on windows 7. Could we be using different kinds of code?

 

[jae113]

YOU ARE AWESOME!!!!!!!!!!!!!!! It works


Do you have a second? Could you explain some of that to me, please?

How do you say all this ... plainly? I'm reading it something like, "Get WSD, we're going to sort, we're going to sort the fields" . "Clear any selections that you have" . "Sort by column AO, sort descending on values"

But the Range part I don't really understand

With WSD.Sort.SortFields
.Clear
.Add Key:=WSD.Range("AO1"), 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
End With

 

[RoryA]

That's correct. The Range property has a few syntaxes - this one is the Range(start_cell, end_cell) version. You can use either a range address (or name) or a range object for either of the arguments - here we use "A1" as the starting address, and a range object as the end cell:

wsd.Cells(finalRow, finalCol)

Does that help?

 

[jae113]

Yes! so the Object in this case is "in WSD select all the cells that have data starting in A1 and going to the predefined FinalRow to the predefined FinalCol"

Is that it?

You are so awesome! thanks so so much!

 

[RoryA]

That's correct.

 

[jae113]

Hello again!

I'm getting that pesky runtime 1004 error again, only this time at .Apply!
Here is the code:

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


I have my ranges, they're defined, I said that I have a header (which I do), so I don't understand why it doesn't like Apply.

Any suggestions?

 

[jae113]

BTW, I had to import a backup of the VBA code because my PERSONAL workbook got corrupted some how.

It did say that it had to remove a sort row, but I don't see what or why

I can post the whole series of messy code if anyone thinks that will help.

Thanks!

 

[RoryA]

What are the values of finalRow and finalCol?