Recorded macro need to adjust for different size reports

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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
603
Michael M
Michael M
B
  • Solved
VBScript to create pivot for entire data on a tab
Replies
9
Views
3K
Domenic
D
M
  • Question
VBA to escape to next set of code if no data were found
Replies
1
Views
619
cmowla
C
A
  • Question
macro two lists into one pivot
Replies
0
Views
325
andyT7
A

Forum statistics

Threads
1,215,357
Messages
6,124,483
Members
449,165
Latest member
ChipDude83

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