Cleaning up code

khreid1

New Member
Joined
Jun 1, 2012
Messages
7
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:

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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Here is part 1 & 2 cleaned up. As you have recorded you can look at the code and adjust as indicated. When you want to do a number of things with a sheet think about using a with statement.

HTML:
Sub change1()
****Cells.Copy
****Sheets.Add After:=Sheets(Sheets.Count)
****ActiveSheet.Paste
****Sheets("test").Name = "Picking"
****Sheets("Sheet1").Name = "Putaway"
****
End Sub

Sub change2()
' Autofill
'
****With Sheets("Picking").Select
********.Range("M1") = "Totals"
********.Range("M2") = "1"
********.Range("M3") = "2"
********.Range("M4") = "3"
********.Range("M2:M4").Select
********.Selection.AutoFill Destination:=Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row)
****End With
****With Sheets("Putaway").Select
********.Range("M1") = "Totals"
********.Range("M2") = "1"
********.Range("M3") = "2"
********.Range("M4") = "3"
********.Range("M2:M4").Select
********.Selection.AutoFill Destination:=Range("M2:M" & Range("A" & Rows.Count).End(xlUp).Row)
****End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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