Macro Code Help - File name changes

C

CM722

New Member
Joined
Jan 13, 2016
Messages
16
Hello,

I am somewhat new to recording macros and editing the code. I can do basic code editing, but get stuck with anything overly complex. I have recorded a macro and I basically need it to open a file each day (the current day) and rename it to a new file with the current day's date. I then need it to open another file with the previous day's date on the end. I am hoping someone can help me, I have my code below (sensitive information removed) and have added on the end of the file name where I want the file to be the current or previous day. I also need to figure out how to make this work on Monday's when it would be pulling data from Friday. I did find other topics on the forum addressing this, but I wasn't able to make sense of how to add it to my code as I am an amateur when it comes to macros. Can anyone help me with this?

Sub Macro2()
'
' Macro2 Macro
'

'
Workbooks.Open Filename:= _
"N:\zApps\Reports\Backlog\A\B_currentday.xlsx"
Cells.Select
Selection.AutoFilter
Columns("A:AS").Select
Selection.EntireColumn.Hidden = False
ActiveWorkbook.SaveAs Filename:= _
"N:\Planning\CommonRW\A\B\C\("daily ships_ORL_Backlog _”currentday.xlsx").
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Workbooks.Open Filename:= _
"N:\Planning\CommonRW\A\B\C\("daily ships_ORL_Backlog _”previousday.xlsx").
Columns("A:C").Select
Range("C1").Activate
Selection.EntireColumn.Hidden = False
Columns("C:BD").Select
Selection.EntireColumn.Hidden = False
Rows("1:1").Select
Selection.Copy
Windows("("daily ships_ORL_Backlog _”currentday.xlsx"). _"). _
Activate
Rows("1:1").Select
ActiveSheet.Paste
Windows("daily ships_ORL_Backlog _”previous day”.xlsx ").Activate
Application.CutCopyMode = False
Windows("daily ships_ORL_Backlog _”currentday.xlsx"). _
Activate
Sheets("STR").Select
Sheets("STR").Move After:=Sheets(5)
Cells.Select
Selection.ClearContents
Sheets("STR").Select
Sheets("STR").Name = "Sheet1"
Sheets("samples").Select
ActiveSheet.Range("$A$1:$AS$18").AutoFilter Field:=1, Criteria1:= _
"X"
Rows("14:230").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-153
ActiveSheet.Range("$A$1:$AS$18").AutoFilter Field:=1, Criteria1:="<>"
ActiveWindow.SmallScroll Down:=-45
Rows("2:176").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-18
ActiveSheet.Range("$A$1:$AS$18").AutoFilter Field:=1
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 90
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
Sheets("returns").Select
ActiveSheet.Range("$A$1:$AS$56").AutoFilter Field:=1, Criteria1:= _
"X”
Rows("48:204").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-87
ActiveSheet.Range("$A$1:$AS$56").AutoFilter Field:=1, Criteria1:="<>"
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 73
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 55
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
Rows("2:249").Select
Selection.ClearContents
Range("A222").Select
ActiveSheet.Range("$A$1:$AS$56").AutoFilter Field:=1
ActiveWindow.SmallScroll Down:=-12
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 181
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
Sheets("backlog").Select
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=1, Criteria1:=Array( _
"X", "Y", "Z"), Operator:= _
xlFilterValues
Rows("2:7161").Select
Selection.ClearContents
ActiveWindow.ScrollRow = 7122
ActiveWindow.ScrollRow = 7112
ActiveWindow.ScrollRow = 7074
ActiveWindow.ScrollRow = 7046
ActiveWindow.ScrollRow = 6326
ActiveWindow.ScrollRow = 5947
ActiveWindow.ScrollRow = 4953
ActiveWindow.ScrollRow = 4594
ActiveWindow.ScrollRow = 3864
ActiveWindow.ScrollRow = 3438
ActiveWindow.ScrollRow = 2596
ActiveWindow.ScrollRow = 2406
ActiveWindow.ScrollRow = 1507
ActiveWindow.ScrollRow = 1213
ActiveWindow.ScrollRow = 702
ActiveWindow.ScrollRow = 465
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 2
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=1, Criteria1:="<>"
ActiveWorkbook.Worksheets("backlog").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("backlog").AutoFilter.Sort.SortFields.Add Key:= _
Range("A1:A3782"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("backlog").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-12
ActiveWindow.ScrollRow = 2
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=1
ActiveWindow.SmallScroll Down:=-27
ActiveWorkbook.Worksheets("backlog").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("backlog").AutoFilter.Sort.SortFields.Add Key:= _
Range("A1:A3782"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortNormal
With ActiveWorkbook.Worksheets("backlog").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll Down:=-15
Range("C2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[2],RC[3],RC[4])"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C1158"), Type:=xlFillDefault
Range("C2:C1158").Select
ActiveWindow.ScrollRow = 1120
ActiveWindow.ScrollRow = 1115
ActiveWindow.ScrollRow = 1110
ActiveWindow.ScrollRow = 1095
ActiveWindow.ScrollRow = 1080
ActiveWindow.ScrollRow = 946
ActiveWindow.ScrollRow = 618
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 2
Columns("C:C").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:B").Select
Range("B1").Activate
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("N:N").Select
Selection.EntireColumn.Hidden = True
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 16
Columns("AF:AJ").Select
Selection.EntireColumn.Hidden = True
Columns("AL:AS").Select
Selection.EntireColumn.Hidden = True
Range("AT2").Select
ActiveWindow.SmallScroll Down:=-15
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-43],'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R2327C56,44,FALSE)"
Range("AT2").Select
Selection.AutoFill Destination:=Range("AT2:AT10"), Type:=xlFillDefault
Range("AT2:AT10").Select
Range("AT10").Select
Selection.AutoFill Destination:=Range("AT10:AT722"), Type:=xlFillDefault
Range("AT10:AT722").Select
Selection.AutoFill Destination:=Range("AT10:AT820"), Type:=xlFillDefault
Range("AT10:AT820").Select
ActiveWindow.ScrollRow = 777
ActiveWindow.ScrollRow = 772
ActiveWindow.ScrollRow = 767
ActiveWindow.ScrollRow = 757
ActiveWindow.ScrollRow = 732
ActiveWindow.ScrollRow = 673
ActiveWindow.ScrollRow = 538
ActiveWindow.ScrollRow = 484
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 335
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 2
Columns("AT:AT").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=46, Criteria1:="=0", _
Operator:=xlOr, Criteria2:="=#N/A"
Range("AT180:AT1159").Select
ActiveWindow.SmallScroll Down:=9
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-27
ActiveWindow.ScrollRow = 759
ActiveWindow.ScrollRow = 735
ActiveWindow.ScrollRow = 695
ActiveWindow.ScrollRow = 617
ActiveWindow.ScrollRow = 583
ActiveWindow.ScrollRow = 182
ActiveWindow.ScrollRow = 104
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=46
Range("AT180").Select
ActiveWindow.SmallScroll Down:=12
Range("AT222").Select
ActiveWindow.SmallScroll Down:=-180
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 2
ActiveWindow.SmallScroll Down:=-12
Range("AT3").Select
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 2
Range("AU2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-44],'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1049C56,45,FALSE)"
Range("AU2").Select
Columns("AT:AT").EntireColumn.AutoFit
Selection.AutoFill Destination:=Range("AU2:AU817"), Type:=xlFillDefault
Range("AU2:AU817").Select
ActiveWindow.ScrollRow = 777
ActiveWindow.ScrollRow = 772
ActiveWindow.ScrollRow = 767
ActiveWindow.ScrollRow = 762
ActiveWindow.ScrollRow = 757
ActiveWindow.ScrollRow = 752
ActiveWindow.ScrollRow = 742
ActiveWindow.ScrollRow = 732
ActiveWindow.ScrollRow = 678
ActiveWindow.ScrollRow = 658
ActiveWindow.ScrollRow = 340
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 2
Range("AU2").Select
Selection.Copy
Range("AV2").Select
ActiveSheet.Paste
Range("AU2").Select
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=47, Criteria1:=Array( _
"#N/A", "0", "="), Operator:=xlFilterValues
Range("AU2:AU994").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.ScrollRow = 956
ActiveWindow.ScrollRow = 951
ActiveWindow.ScrollRow = 936
ActiveWindow.ScrollRow = 931
ActiveWindow.ScrollRow = 921
ActiveWindow.ScrollRow = 916
ActiveWindow.ScrollRow = 906
ActiveWindow.ScrollRow = 892
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 713
ActiveWindow.ScrollRow = 628
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 2
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=47
Columns("AU:AU").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AU28").Select
Columns("AU:AU").EntireColumn.AutoFit
Range("AV2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1049C56,46,FALSE)"
Range("AV2").Select
Selection.AutoFill Destination:=Range("AV2:AV781"), Type:=xlFillDefault
Range("AV2:AV781").Select
Selection.AutoFill Destination:=Range("AV2:AV800"), Type:=xlFillDefault
Range("AV2:AV800").Select
ActiveWindow.ScrollRow = 757
ActiveWindow.ScrollRow = 752
ActiveWindow.ScrollRow = 747
ActiveWindow.ScrollRow = 727
ActiveWindow.ScrollRow = 722
ActiveWindow.ScrollRow = 683
ActiveWindow.ScrollRow = 638
ActiveWindow.ScrollRow = 464
ActiveWindow.ScrollRow = 350
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 2
Range("AV2").Select
Columns("AV:AV").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.SmallScroll ToRight:=2
Selection.Copy
ActiveWindow.ScrollColumn = 49
Range("AW2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1049C56,47,FALSE)"
Range("AW3").Select
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 38
ActiveWindow.SmallScroll ToRight:=2
Columns("L:L").ColumnWidth = 30
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
Columns("L:L").ColumnWidth = 17.86
Columns("L:L").ColumnWidth = 15.71
Range("AV2").Select
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
Columns("AV:AV").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AV1").Select
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
Columns("AU:AV").Select
Range("AV1").Activate
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.SmallScroll ToRight:=-1
ActiveWindow.Zoom = 90
ActiveWindow.Zoom = 80
Selection.ColumnWidth = 97
Range("AV1").Select
Columns("AU:AU").ColumnWidth = 12.29
Columns("AV:AV").ColumnWidth = 35.14
Columns("AV:AV").Select
Application.CutCopyMode = False
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("AV2").Select
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=48, Criteria1:=Array( _
"#N/A", "0", "="), Operator:=xlFilterValues
Range("AV29:AV800").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-15
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=48
ActiveWindow.ScrollRow = 779
ActiveWindow.ScrollRow = 769
ActiveWindow.ScrollRow = 755
ActiveWindow.ScrollRow = 740
ActiveWindow.ScrollRow = 725
ActiveWindow.ScrollRow = 651
ActiveWindow.ScrollRow = 616
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 180
ActiveWindow.ScrollRow = 2
Range("AW2").Select
Selection.AutoFill Destination:=Range("AW2:AW841"), Type:=xlFillDefault
Range("AW2:AW841").Select
Columns("AW:AW").EntireColumn.AutoFit
ActiveWindow.ScrollRow = 789
ActiveWindow.ScrollRow = 779
ActiveWindow.ScrollRow = 760
ActiveWindow.ScrollRow = 700
ActiveWindow.ScrollRow = 665
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 2
Columns("AW:AW").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=49, Criteria1:="=0", _
Operator:=xlOr, Criteria2:="=#N/A"
Range("AW2:AW3846").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.ScrollRow = 3797
ActiveWindow.ScrollRow = 3787
ActiveWindow.ScrollRow = 3777
ActiveWindow.ScrollRow = 836
ActiveWindow.ScrollRow = 820
ActiveWindow.ScrollRow = 790
ActiveWindow.ScrollRow = 775
ActiveWindow.ScrollRow = 709
ActiveWindow.ScrollRow = 704
ActiveWindow.ScrollRow = 684
ActiveWindow.ScrollRow = 674
ActiveWindow.ScrollRow = 648
ActiveWindow.ScrollRow = 633
ActiveWindow.ScrollRow = 568
ActiveWindow.ScrollRow = 517
ActiveWindow.ScrollRow = 456
ActiveWindow.ScrollRow = 416
ActiveWindow.ScrollRow = 325
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 2
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=49
Range("AX2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1000C56,48,FALSE)"
Range("AX2").Select
Selection.Copy
Range("AY2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1000C56,49,FALSE)"
Range("AY2").Select
Selection.Copy
Range("AZ2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1000C56,50,FALSE)"
Range("AZ2").Select
Selection.Copy
Range("BA2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx.xlsx]backlog'!R1C3:R1000C56,51,FALSE)"
Range("BA2").Select
Selection.Copy
Range("BB2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1000C56,52,FALSE)"
Range("BB2").Select
Selection.Copy
Range("BC2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1000C56,53,FALSE)"
Range("BC2").Select
Selection.Copy
Range("BD2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC3,'[daily ships_ORL_Backlog _”previous day”.xlsx]backlog'!R1C3:R1000C56,54,FALSE)"
Range("BD3").Select
Columns("BD:BD").EntireColumn.AutoFit
ActiveWindow.Zoom = 90
ActiveWindow.Zoom = 100
Range("AX2").Select
Selection.AutoFill Destination:=Range("AX2:AX852"), Type:=xlFillDefault
Range("AX2:AX852").Select
ActiveWindow.ScrollRow = 812
ActiveWindow.ScrollRow = 802
ActiveWindow.ScrollRow = 797
ActiveWindow.ScrollRow = 767
ActiveWindow.ScrollRow = 722
ActiveWindow.ScrollRow = 633
ActiveWindow.ScrollRow = 618
ActiveWindow.ScrollRow = 469
ActiveWindow.ScrollRow = 359
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 2
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=50, Criteria1:=Array( _
"#N/A", "0", "="), Operator:=xlFilterValues
Range("AX2:AX903").Select
Selection.ClearContents
ActiveWindow.ScrollRow = 862
ActiveWindow.ScrollRow = 857
ActiveWindow.ScrollRow = 852
ActiveWindow.ScrollRow = 847
ActiveWindow.ScrollRow = 842
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 717
ActiveWindow.ScrollRow = 593
ActiveWindow.ScrollRow = 459
ActiveWindow.ScrollRow = 444
ActiveWindow.ScrollRow = 260
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 2
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=50
Columns("AX:AX").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AY2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AY2:AY831"), Type:=xlFillDefault
Range("AY2:AY831").Select
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=51, Criteria1:=Array( _
"#N/A", "0", "="), Operator:=xlFilterValues
Range("AY2:AY831").Select
Range("AY831").Activate
Selection.ClearContents
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=51
Columns("AY:AY").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AZ2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AZ2:AZ1093"), Type:=xlFillDefault
Range("AZ2:AZ1093").Select
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=52, Criteria1:=Array( _
"#N/A", "0", "="), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-84
Selection.ClearContents
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=52
ActiveWindow.ScrollRow = 1006
ActiveWindow.ScrollRow = 1001
ActiveWindow.ScrollRow = 996
ActiveWindow.ScrollRow = 991
ActiveWindow.ScrollRow = 986
ActiveWindow.ScrollRow = 981
ActiveWindow.ScrollRow = 971
ActiveWindow.ScrollRow = 961
ActiveWindow.ScrollRow = 941
ActiveWindow.ScrollRow = 916
ActiveWindow.ScrollRow = 817
ActiveWindow.ScrollRow = 797
ActiveWindow.ScrollRow = 727
ActiveWindow.ScrollRow = 658
ActiveWindow.ScrollRow = 355
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 2
Columns("AZ:AZ").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BA2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BA2:BA990"), Type:=xlFillDefault
Range("BA2:BA990").Select
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=53, Criteria1:="<>"
Selection.ClearContents
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=53
Columns("BA:BA").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollRow = 946
ActiveWindow.ScrollRow = 911
ActiveWindow.ScrollRow = 678
ActiveWindow.ScrollRow = 648
ActiveWindow.ScrollRow = 439
ActiveWindow.ScrollRow = 374
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 2
Range("BB2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BB2:BB848"), Type:=xlFillDefault
Range("BB2:BB848").Select
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=54, Criteria1:=Array( _
"#N/A", "0", "="), Operator:=xlFilterValues
Selection.ClearContents
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=54
Columns("BB:BB").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollRow = 812
ActiveWindow.ScrollRow = 807
ActiveWindow.ScrollRow = 802
ActiveWindow.ScrollRow = 782
ActiveWindow.ScrollRow = 628
ActiveWindow.ScrollRow = 583
ActiveWindow.ScrollRow = 379
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 2
Range("BC2").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("BC2:BC1001"), Type:=xlFillDefault
Range("BC2:BC1001").Select
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=55, Criteria1:=Array( _
"#N/A", "0", "="), Operator:=xlFilterValues
Selection.ClearContents
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=55
Columns("BC:BC").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.ScrollRow = 981
ActiveWindow.ScrollRow = 976
ActiveWindow.ScrollRow = 971
ActiveWindow.ScrollRow = 966
ActiveWindow.ScrollRow = 936
ActiveWindow.ScrollRow = 916
ActiveWindow.ScrollRow = 658
ActiveWindow.ScrollRow = 588
ActiveWindow.ScrollRow = 369
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 2
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=56, Criteria1:="<>"
Range("BD2").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveSheet.Range("$A$1:$BD$3782").AutoFilter Field:=56
Columns("BD:BD").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("BD2").Select
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 13
Cells.Select
Application.CutCopyMode = False

End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
What do you mean by current day? Do you mean the day name i.e. Monday, Tuesday, etc.? If so, add this code to get the current day and previous day, depending on the current date:
Code: 
    Dim currentDay As String, previousDay As String
    
    currentDay = Format(Date, "Dddd")
    If Weekday(Date) = vbMonday Then
        previousDay = Format(Date - 3, "Dddd")
    Else
        previousDay = Format(Date - 1, "Dddd")
    End If
Change "Dddd" to "Ddd" if you want short day names, Mon, Tue, etc.

You can then change your code to:
Code: 
Workbooks.Open Filename:="N:\zApps\Reports\Backlog\A\B_" & currentDay & ".xlsx"
and similar elsewhere.

For saving with the current date (yyyymmdd format, change as required):
Code: 
    ActiveWorkbook.SaveAs Filename:= _
        "N:\Planning\CommonRW\A\B\C\daily ships_ORL_Backlog _" & Format(Date, "yyyymmdd") & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Please use CODE tags - the # icon in the message editor.
 
Last edited:
Upvote 0
Thank you very much, I think this is what I need. By current day, I mean I just want the first file to open with today's date and then get saved with a new name but today's date in yyyyddmm format. Then there is a secondary file with the yyyyddmm format but from the previous day (except on Mondays, then it is from Friday). The previous day file is the basis for all the vlookups in my new file so I need my code in the vlookups to reflect the file from the previous day. Should all the vlookups look like this or is that not necessary since the file is already open?
Code: 
[COLOR=#574123]"=VLOOKUP(RC[-43],'[daily ships_ORL_Backlog _”yyyyddmm-1”.xlsx]backlog'!R1C3:R2327C56,44,FALSE)"[/COLOR]
[COLOR=#574123]Range("AT2").Selec[/COLOR]

So if I understand correctly I basically need to put in a conditional statement at the beginning of the file and choose a place holder for the current or previous date? Or can I just do this for the previous day and current day:

Code: 
Workbooks.Open Filename:="N:\zApps\Reports\Backlog\A\B_" & (yyyymmdd-1) & ".xlsx"

Also, where should I insert the DIM? I'm not really clear on what dim is exactly or where to insert it into my code, but this feedback looks like it should work.

Sorry for all the questions, the coding part is confusing for me beyond basic editing.
 
Upvote 0
You haven't said what the date format of the current day file name is. If it is yyyymmdd (e.g. "N:\zApps\Reports\Backlog\A\B_20160218.xlsx") then the Format function call will need changing to reflect this particular format.

When a VLOOKUP references another workbook, that workbook must be open and the VLOOKUP must refer to the correct workbook name. Your code therefore has to change the VLOOKUPs to reflect the name of the previous day's workbook.

The Dim statement is used to declare a variable, i.e. tell VBA that the variable exists and what type of data it will contain. The Dim should be placed before the variable is used, usually grouped with all the other Dims at the top of the routine or function.

Here I have modified the first few lines of your code to use the current and previous day workbook names.
Code: 
Sub Macro2()
'
' Macro2 Macro
'

    Dim currentDay As String, previousDay As String
    Dim currentDayWorkbook As String, previousDayWorkbook As String
    
    currentDay = Format(Date, "yyyymmdd")
    If Weekday(Date) = vbMonday Then
        previousDay = Format(Date - 3, "yyyymmdd")  'Friday
    Else
        previousDay = Format(Date - 1, "yyyymmdd")
    End If
    
    currentDayWorkbook = "daily ships_ORL_Backlog _" & currentDay & ".xlsx"
    previousDayWorkbook = "daily ships_ORL_Backlog _" & previousDay & ".xlsx"
    
    Workbooks.Open Filename:="N:\zApps\Reports\Backlog\A\B_" & currentDay & ".xlsx"
    Cells.Select
    Selection.AutoFilter
    Columns("A:AS").Select
    Selection.EntireColumn.Hidden = False
    ActiveWorkbook.SaveAs Filename:="N:\Planning\CommonRW\A\B\C\" & currentDayWorkbook, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Workbooks.Open Filename:="N:\Planning\CommonRW\A\B\C\" & previousDayWorkbook
    Columns("A:C").Select
    Range("C1").Activate
    Selection.EntireColumn.Hidden = False
    Columns("C:BD").Select
    Selection.EntireColumn.Hidden = False
    Rows("1:1").Select
    Selection.Copy
    Windows(currentDayWorkbook).Activate
    Rows("1:1").Select
    ActiveSheet.Paste
    Windows(previousDayWorkbook).Activate
    Application.CutCopyMode = False
    Windows(currentDayWorkbook).Activate
Further down, you will need to modify the VLOOKUP lines like this to use the previousDayWorkbook variable.
Code: 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-43],'[" & previousDayWorkbook & "]backlog'!R1C3:R2327C56,44,FALSE)"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
142
Coyotex3
C
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
662
cstuder
C
An Quala
  • Question
Should I name it Active.Sheet or sheet name?
Replies
8
Views
627
MARK858
MARK858
D
  • Question
Macro to Keep Sheet Names as Sheet1 and Sheet2
2
Replies
14
Views
831
djmnon
D
J
  • Question
Issue with Macros and renaming files
Replies
6
Views
476
kevin9999
K

Forum statistics

Threads
1,214,405
Messages
6,119,320
Members
448,887
Latest member
AirOliver

Share this page

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