[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Macro

Posted on 2012-08-25
5
Medium Priority
?
810 Views
Last Modified: 2012-08-26
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
0
Comment
Question by:Edward Pamias
  • 3
  • 2
5 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 38333581
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
 
LVL 19

Author Comment

by:Edward Pamias
ID: 38333974
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
 
LVL 19

Author Comment

by:Edward Pamias
ID: 38333985
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38334226
Maybe you should open another question. This one was specifically for variable number of rows and is working as such.
0
 
LVL 19

Author Closing Comment

by:Edward Pamias
ID: 38334233
Thank you for your help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
New style of hardware planning for Microsoft Exchange server.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question