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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,700
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,839
Members
413,943
Latest member
Dhornsby21

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
Top