Hey Everyone,
I am very new to VBA but am very hopeful on improving my skills to help me at work.
I recently completed my first code to save time at work, the problem is that the code is very long and messy. Mostly because I had to adapt from a recorded macro for some stuff I couldnt find in my VBA books or got errors.
Currently the code is a 45 page word document saved on my Work computer, and looks something like this:
I feel this is very messy after reading through these forums as most of the code suggestions dont have .select. There is also the issue of my selection copy not being labelled in the coding.
Can I just get rid of the Activecell formula and go straight to the Autofill?
I really want to clean up my pivot table, especially as I use these a lot, however the code from my books was very ineffective and wasnt well explained.
Finally I wanted to be able to close the SummarySheet so that it doesnt stay open on my computer. Any suggestions?
Thanks for all help in advance
I am very new to VBA but am very hopeful on improving my skills to help me at work.
I recently completed my first code to save time at work, the problem is that the code is very long and messy. Mostly because I had to adapt from a recorded macro for some stuff I couldnt find in my VBA books or got errors.
Currently the code is a 45 page word document saved on my Work computer, and looks something like this:
Code:
' CopyPaste</SPAN>
'</SPAN>
Cells.Select</SPAN>
Selection.Copy</SPAN>
Sheets.Add After:=Sheets(Sheets.Count)</SPAN>
ActiveSheet.Paste</SPAN>
Sheets("test").Select</SPAN>
Sheets("test").Name = "Picking"</SPAN>
Sheets("Sheet1").Select</SPAN>
Sheets("Sheet1").Name = "Putaway"</SPAN>
Cells.Select</SPAN>
I feel this is very messy after reading through these forums as most of the code suggestions dont have .select. There is also the issue of my selection copy not being labelled in the coding.
Code:
' Autofill</SPAN>
'</SPAN>
Sheets("Picking").Select</SPAN>
Range("M1").Select</SPAN>
ActiveCell.FormulaR1C1 = "Totals"</SPAN>
Range("M2").Select</SPAN>
ActiveCell.FormulaR1C1 = "1"</SPAN>
Range("M3").Select</SPAN>
ActiveCell.FormulaR1C1 = "2"</SPAN>
Range("M4").Select</SPAN>
ActiveCell.FormulaR1C1 = "3"</SPAN>
Range("M2:M4").Select</SPAN>
Selection.AutoFill Destination:=Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row)</SPAN>
Sheets("Putaway").Select</SPAN>
Range("M1").Select</SPAN>
ActiveCell.FormulaR1C1 = "Totals"</SPAN>
Range("M2").Select</SPAN>
ActiveCell.FormulaR1C1 = "1"</SPAN>
Range("M3").Select</SPAN>
ActiveCell.FormulaR1C1 = "2"</SPAN>
Range("M4").Select</SPAN>
ActiveCell.FormulaR1C1 = "3"</SPAN>
Range("M2:M4").Select</SPAN>
Selection.AutoFill Destination:=Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row)</SPAN>
Can I just get rid of the Activecell formula and go straight to the Autofill?
Code:
' Picking Pivot Table</SPAN>
'</SPAN>
Sheets("Picking").Select</SPAN>
Cells.Select</SPAN>
Sheets.Add</SPAN>
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _</SPAN>
"Picking!R1C1:R1048576C13", Version:=xlPivotTableVersion12).CreatePivotTable _</SPAN>
TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _</SPAN>
:=xlPivotTableVersion12</SPAN>
Sheets("Sheet2").Select</SPAN>
Cells(3, 1).Select</SPAN>
With ActiveSheet.PivotTables("PivotTable1").PivotFields("USER")</SPAN>
.Orientation = xlRowField</SPAN>
.Position = 1</SPAN>
End With</SPAN>
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Totals")</SPAN>
.Orientation = xlRowField</SPAN>
.Position = 2</SPAN>
End With</SPAN>
With ActiveSheet.PivotTables("PivotTable1").PivotFields("QUANTITY")</SPAN>
.Orientation = xlRowField</SPAN>
.Position = 3</SPAN>
End With</SPAN>
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _</SPAN>
"PivotTable1").PivotFields("Totals"), "Count of Totals", xlCount</SPAN>
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _</SPAN>
"PivotTable1").PivotFields("QUANTITY"), "Count of QUANTITY", xlCount</SPAN>
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of QUANTITY")</SPAN>
.Caption = "Sum of QUANTITY"</SPAN>
.Function = xlSum</SPAN>
End With</SPAN>
I really want to clean up my pivot table, especially as I use these a lot, however the code from my books was very ineffective and wasnt well explained.
Code:
' Add Summary Sheet</SPAN>
'</SPAN>
Sheets.Add After:=Sheets(Sheets.Count)</SPAN>
Sheets("Sheet4").Select</SPAN>
Sheets("Sheet4").Name = "Summary"</SPAN>
Workbooks.Open Filename:= _</SPAN>
"U:\PICKING PUTAWAY FOLDER\Fiscal Year 2012\SummarySheet.xlsx"</SPAN>
Range("A1:W50").Select</SPAN>
Selection.Copy</SPAN>
Windows("test.csv").Activate</SPAN>
Range("A1").Select</SPAN>
ActiveSheet.Paste</SPAN>
Finally I wanted to be able to close the SummarySheet so that it doesnt stay open on my computer. Any suggestions?
Thanks for all help in advance