Recorded macro need to adjust for different size reports

  • Thread starter Thread starter Legacy 216151
  • Start date Start date
L

Legacy 216151

Guest
I have manually recorded the macro below for repetitive tasks done on a frequent basis. However, I need the macro to be able to pick up all of the data. From month to month the amount of lines may change. Is it possible to code the sections that I highlighted in blue below to read all data no matter how many different lines?

Thanks in advance,

Nick

Code: 
Sub Test()
'
' Test Macro
'
'
Cells.Select
Cells.EntireColumn.AutoFit
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("1:3,5:5").Select
Range("A5").Activate
Selection.Delete Shift:=xlUp
Rows("1:1").Select
Selection.Font.Bold = True
Cells.Select
ActiveWorkbook.Worksheets("073112 ZMBL 7XXX").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("073112 ZMBL 7XXX").Sort.SortFields.Add Key:=Range( _
[COLOR=#0000ff]"A2:A249"), [/COLOR]SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("073112 ZMBL 7XXX").Sort.SortFields.Add Key:=Range( _
[COLOR=#0000ff]"B2:B249"), [/COLOR]SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("073112 ZMBL 7XXX").Sort
.SetRange Rows[COLOR=#0000ff]("1:249")
[/COLOR].Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll ToRight:=5
Columns("U:U").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Columns("V:V").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("U2").Select
Workbooks.Open Filename:= _
"P:\Procurement - Materials - Logistics\Materials\Inventory Master Reports\Cost Center MASTER LISTING(KS13) (version 1).xls" _
, Origin:=xlWindows
Windows("073112 ZMBL 7XXX").Activate
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[Cost Center MASTER LISTING(KS13) (version 1).xls]JAN-10 GOM CCTR MASTER LISTING('!R1C6:R491C8,2,FALSE)"
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R2C20,'[Cost Center MASTER LISTING(KS13) (version 1).xls]JAN-10 GOM CCTR MASTER LISTING('!R1C6:R491C8,2,FALSE)"
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:V2"), Type:=xlFillDefault
Range("U2:V2").Select
Range("V2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R2C20,'[Cost Center MASTER LISTING(KS13) (version 1).xls]JAN-10 GOM CCTR MASTER LISTING('!R1C6:R491C8,3,FALSE)"
Range("U2:V2").Select
Selection.AutoFill Destination:=Range("U2:V244"), Type:=xlFillDefault
Range("U2:V244").Select
ActiveWindow.SmallScroll Down:=-231
Columns("U:V").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll ToRight:=-5
Rows("1:1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 329
ActiveWindow.ScrollColumn = 383
ActiveWindow.ScrollColumn = 411
ActiveWindow.ScrollColumn = 656
ActiveWindow.ScrollColumn = 629
ActiveWindow.ScrollColumn = 219
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll ToRight:=4
Range("U1").Select
ActiveCell.FormulaR1C1 = "PSL"
Range("V1").Select
ActiveCell.FormulaR1C1 = "SubPSL"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"073112 ZMBL 7XXX![COLOR=#0000ff]R1C1:R244C26[/COLOR]", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Plant")
.Orientation = xlPageField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(" Value of T"), "Sum of Value of T", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PSL")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("SubPSL")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Profit Cen")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Plant").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Material ").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Description ").Subtotals = Array(False, False, False _
, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Storage Lo").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Storage Bi").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields(" Unrestrict").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields(" Committed").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Safety Sto").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Price Unit").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Price cont").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("10 Valuati").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Local Curr").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("30 Valuati").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("31 Valuati").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("CC phys. i").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Procuremen").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("MRP Type").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Haz. mater").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Profit Cen").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("PSL").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("SubPSL").Subtotals = Array( _
False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields(" Value of T").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Last goods").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Plant Dele").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("DF stor. l").Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow
Columns("D:D").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Summary"
Range("C36").Select
ActiveWindow.SmallScroll Down:=-18
ActiveWorkbook.SaveAs Filename:= _
"P:\Procurement - Materials - Logistics\Materials\Inventory Master Reports\ZMBL - Storage Locs\7XXX stock\2012 ZMBL 7XXX\Test.xls" _
, FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End sub
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
add a variable for the last row

Code: 
Dim LR as Long
LR = Range("A" & Rows.Count).End(xlUp).Row

then you can use that like this

ActiveWorkbook.Worksheets("073112 ZMBL 7XXX").Sort.SortFields.Add Key:=Range( _
[COLOR=#0000ff]   "A2:A" & LR), [/COLOR]SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
   xlSortNormal
 
Upvote 0
Texasalynn,

That seems to work up until the ponit where the pivot table is selecting the source data. How can I incorporate that into the part of the code below?

Thanks


Code: 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"073112 ZMBL 7XXX![COLOR=#0000ff]R1C1:R244C26[/COLOR]", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
[\Code]
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Recording macro pivot table error
Replies
2
Views
1K
biglb79
B
A
  • Solved
Changing recorded macro to have dynamic selection of cells when trying to format a sheet.
Replies
4
Views
850
Michael M
Michael M
B
  • Solved
VBScript to create pivot for entire data on a tab
Replies
9
Views
3K
Domenic
D
M
  • Question Question
VBA to escape to next set of code if no data were found
Replies
1
Views
734
cmowla
C
A
  • Question Question
macro two lists into one pivot
Replies
0
Views
381
andyT7
A

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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