Hi There,
I am stuck here, I am trying out macros to open and copy data from different excel files in a folder and paste it in the master sheet(monitor data).however sometimes these excel files are not submitted at the same time,therefore there will be an error (debug) as that file name would not be present at that point in time.So I would to have a code that will help me to “SKIP” or loop through that excel File Name(if it does not exist) and all other procedures that are subsequent and go to the next excel file name(exist) and its procedures and run.
This is an example of the proc: (with only one name):
Sub copypaste()’
‘ copypaste Macro
‘
1. (existing file in the folder)
Workbooks.Open Filename:= _
“c:01 Consumer Credit ReportsTest_Form 39”File name“.xlsb”
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets(“Sum_of_Agreement”).Select
ActiveWindow.ScrollColumn = 1
Range(“A2:F5”).Select
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q11”).Activate
Range(“A166”).Select
Range(“A” & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
Windows(“”File name“.xlsb”).Activate
Sheets(“Provincial_Distribution”).Select
Range(“A2:F11”).Select
Application.CutCopyMode = False
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q11”).Activate
Sheets(“Prov Distribution”).Select
Range(“A412”).Select
Range(“A” & Rows.Count).End(xlUp).Offset(1).Select
ActiveSheet.Paste
2. (Does not exist yet in the folder)
Workbooks.Open Filename:= _
” Statistical Reports01 Consumer Credit ReportsTest_Form 39″File name“.xlsb”
Sheets(“Sum_of_Agreement”).Select
Range(“E2:F5”).Select
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q1..xltm”).Activate
Sheets(“Sum of CA”).Select
ActiveSheet.Range(“$A$1:$F$10242”).AutoFilter Field:=1, Criteria1:= _
Range(“E7254”).Select
Windows(“”File name“.xlsb”).Activate
Range(“E2:F5”).Select
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q1..xltm”).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(“”File name“.xlsb”).Activate
Sheets(“Provincial_Distribution”).Select
Range(“E2:F11”).Select
Application.CutCopyMode = False
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q1..xltm”).Activate
Sheets(“Prov Distribution”).Select
ActiveSheet.Range(“$A$1:$F$25561”).AutoFilter Field:=1, Criteria1:= _
“”File name”
Range(“E18062”).Select
Windows(“”File name“.xlsb”).Activate
Sheets(“Provincial_Distribution”).Select
Range(“E2:F11”).Select
Application.CutCopyMode = False
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q1..xltm”).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Workbooks.Open Filename:= _
“Statistical Reports01 Consumer Credit ReportsTest_Form 39”File name“.xlsb”
Sheets(“Sum_of_Agreement”).Select
Range(“E2:F5”).Select
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q1..xltm”).Activate
Sheets(“Sum of CA”).Select
ActiveSheet.Range(“$A$1:$F$10242”).AutoFilter Field:=1, Criteria1:= _
“SA Home Loans Group”
Range(“E7254”).Select
Windows(“”File name“.xlsb”).Activate
Range(“E2:F5”).Select
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q1..xltm”).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows(“”File name“.xlsb”).Activate
Sheets(“Provincial_Distribution”).Select
Range(“E2:F11”).Select
Application.CutCopyMode = False
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q1..xltm”).Activate
Sheets(“Prov Distribution”).Select
ActiveSheet.Range(“$A$1:$F$25561”).AutoFilter Field:=1, Criteria1:= _
“”File name“”
Range(“E18062”).Select
Windows(“”File name“.xlsb”).Activate
Sheets(“Provincial_Distribution”).Select
Range(“E2:F11”).Select
Application.CutCopyMode = False
Selection.Copy
Windows(“Monitor Data-2007Q4 to 2018Q1..xltm”).Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
Thanks in advance
