Excel Macro

My macro that I created copy's xxxopenreport.csv to open data on my master sheet. Next I copy xxxclosed report.csv to SLA Data. Next I filter the closed report and copy each day of data to data1 thru data5 sheets on my master sheet. I refresh all my pivots and then copy my lookup table to my trend pasting only the values. The only thing that changes is the number of rows. Is there a way to automate this process using a macro?  See macro below. I need this to work on my weekly CSV file that I copy over to the master sheet. The only the that changes is the date in the beginning of the file and the number of rows in each file.

Here is the macro created....

Sub dailyreport()
'
' dailyreport Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Sheets("Open Data").Select
    Windows("08202012OpenReport.csv").Activate
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("DailyReportool.xlsx:2").Activate
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Windows("08172012ClosedReport.csv").Activate
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DailyReportool.xlsx:2").Activate
    Sheets("SLA Data").Select
    Range("A1").Select
    ActiveSheet.Paste
    Windows("08172012ClosedReport.csv").Activate
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Columns("R:R").ColumnWidth = 15.71
    Columns("X:X").ColumnWidth = 15.71
    Columns("X:X").ColumnWidth = 16.43
    ActiveSheet.Range("$A$1:$AM$1637").AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/13/2012")
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("DailyReportool.xlsx:2").Activate
    Sheets("Data1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Data2").Select
    Windows("08172012ClosedReport.csv").Activate
    ActiveWindow.ScrollRow = 1300
    ActiveWindow.ScrollRow = 1279
    ActiveWindow.ScrollRow = 1257
    ActiveWindow.ScrollRow = 1236
    ActiveWindow.ScrollRow = 1222
    ActiveWindow.ScrollRow = 1204
    ActiveWindow.ScrollRow = 1173
    ActiveWindow.ScrollRow = 1144
    ActiveWindow.ScrollRow = 1117
    ActiveWindow.ScrollRow = 1078
    ActiveWindow.ScrollRow = 1064
    ActiveWindow.ScrollRow = 1037
    ActiveWindow.ScrollRow = 992
    ActiveWindow.ScrollRow = 963
    ActiveWindow.ScrollRow = 935
    ActiveWindow.ScrollRow = 896
    ActiveWindow.ScrollRow = 840
    ActiveWindow.ScrollRow = 783
    ActiveWindow.ScrollRow = 666
    ActiveWindow.ScrollRow = 576
    ActiveWindow.ScrollRow = 506
    ActiveWindow.ScrollRow = 446
    ActiveWindow.ScrollRow = 418
    ActiveWindow.ScrollRow = 395
    ActiveWindow.ScrollRow = 381
    ActiveWindow.ScrollRow = 358
    ActiveWindow.ScrollRow = 317
    ActiveWindow.ScrollRow = 303
    ActiveWindow.ScrollRow = 270
    ActiveWindow.ScrollRow = 249
    ActiveWindow.ScrollRow = 223
    ActiveWindow.ScrollRow = 206
    ActiveWindow.ScrollRow = 116
    ActiveWindow.ScrollRow = 97
    ActiveWindow.ScrollRow = 91
    ActiveWindow.ScrollRow = 77
    ActiveWindow.ScrollRow = 67
    ActiveWindow.ScrollRow = 58
    ActiveWindow.ScrollRow = 50
    ActiveWindow.ScrollRow = 42
    ActiveWindow.ScrollRow = 38
    ActiveWindow.ScrollRow = 34
    ActiveWindow.ScrollRow = 28
    ActiveWindow.ScrollRow = 11
    ActiveWindow.ScrollRow = 1
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Windows("DailyReportool.xlsx:2").Activate
    Sheets("Data2").Select
    Windows("08172012ClosedReport.csv").Activate
    ActiveSheet.Range("$A$1:$AM$1637").AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/14/2012")
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DailyReportool.xlsx:2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Data3").Select
    Windows("08172012ClosedReport.csv").Activate
    Range("A1").Select
    ActiveSheet.Range("$A$1:$AM$1637").AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/15/2012")
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DailyReportool.xlsx:2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Data4").Select
    Windows("08172012ClosedReport.csv").Activate
    Range("A1").Select
    ActiveSheet.Range("$A$1:$AM$1637").AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/16/2012")
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DailyReportool.xlsx:2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Data5").Select
    Windows("08172012ClosedReport.csv").Activate
    Range("A1").Select
    ActiveSheet.Range("$A$1:$AM$1637").AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/17/2012")
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("DailyReportool.xlsx:2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Monday").Select
    Range("B4").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
    Sheets("Tuesday").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    Sheets("Wednesday").Select
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
    Sheets("Thursday").Select
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
    Sheets("Friday").Select
    Range("B3").Select
    ActiveSheet.PivotTables("PivotTable13").PivotCache.Refresh
    Sheets("Lookup").Select
    Selection.Copy
    Sheets("Trend").Select
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="#n/a", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try this modification. I have also deleted a number of redundant lines.

Sub dailyreport()
'
' dailyreport Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Sheets("Open Data").Select
    Windows("08202012OpenReport.csv").Activate
    Range("A1", Range("A1").End(xlToRight).End(xlDown).Address).Copy
    Windows("DailyReportool.xlsx:2").Activate
    ActiveSheet.Paste
    Windows("08172012ClosedReport.csv").Activate
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("DailyReportool.xlsx:2").Activate
    Sheets("SLA Data").Select
    Range("A1").Select
    ActiveSheet.Paste
    Windows("08172012ClosedReport.csv").Activate
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Columns("R:R").ColumnWidth = 15.71
    Columns("X:X").ColumnWidth = 15.71
    Columns("X:X").ColumnWidth = 16.43
    ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/13/2012")
    Range("A1", Range("A1").End(xlToRight).End(xlDown).Address).Copy
    Windows("DailyReportool.xlsx:2").Activate
    Sheets("Data1").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Data2").Select
    Windows("DailyReportool.xlsx:2").Activate
    Sheets("Data2").Select
    Windows("08172012ClosedReport.csv").Activate
    ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/14/2012")
    Range("A1", Range("A1").End(xlToRight).End(xlDown).Address).Copy
    Windows("DailyReportool.xlsx:2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Data3").Select
    Windows("08172012ClosedReport.csv").Activate
    Range("A1").Select
    ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/15/2012")
    Range("A1", Range("A1").End(xlToRight).End(xlDown).Address).Copy
    Windows("DailyReportool.xlsx:2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Data4").Select
    Windows("08172012ClosedReport.csv").Activate
    Range("A1").Select
    ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/16/2012")
    Range("A1", Range("A1").End(xlToRight).End(xlDown).Address).Copy
    Windows("DailyReportool.xlsx:2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Data5").Select
    Windows("08172012ClosedReport.csv").Activate
    Range("A1").Select
    ActiveSheet.Range("$A$1").CurrentRegion.AutoFilter Field:=24, Operator:= _
        xlFilterValues, Criteria2:=Array(2, "8/17/2012")
    Range("A1", Range("A1").End(xlToRight).End(xlDown).Address).Copy
    Windows("DailyReportool.xlsx:2").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Monday").Select
    Range("B4").Select
    Application.CutCopyMode = False
    ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
    Sheets("Tuesday").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    Sheets("Wednesday").Select
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable9").PivotCache.Refresh
    Sheets("Thursday").Select
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable11").PivotCache.Refresh
    Sheets("Friday").Select
    Range("B3").Select
    ActiveSheet.PivotTables("PivotTable13").PivotCache.Refresh
    Sheets("Lookup").Select
    Selection.Copy
    Sheets("Trend").Select
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Replace What:="#n/a", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Open in new window

0
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
This is good for this current csv file but I would need this to work on any csv file. Is there a way you can modify this to take the file name change as well?  For example 08172012ClosedReport.csv  is going to be 08242012ClosedReport.csv the following week.
0
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
In short I just want to open up my 3 files, my master sheet and  XXXopenreport.csv and xxxclosedreport.csv (xxx = current weekending date) and copy the data and refresh the pivots and copy the trend data.
0
 
Saqib Husain, SyedEngineerCommented:
Maybe you should open another question. This one was specifically for variable number of rows and is working as such.
0
 
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thank you for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.