VBA: IF THEN, skip if file not open

[krehkop]

Hello. I don't use macros often (once every few years) but have a need for one now. I have a folder which houses individual monthly files (at year end there will be one for each month, so in theory one is added monthly), there is also a summary file. The example code below is for May and Jun but it'll contain all 12 months. Note, the following works fine if the files exist and all are open. I'd like for the macro to check to see if a file is open and if so, run the macro. In other words, if running it today for the month of Jul and the Jul file is open, it would copy and paste to the summary file... and not error-out on non-existent future months or prior month's files that are not open (i.e., Jan - Jun and Aug - Dec). Additionally, if a file is not open when attempting to run the macro I'd like for a 'Monthly File Not Open' message to appear. Thanks in advance for any assistance.

Sub Test()

'
' Test Macro

fname = Dir("C:\Users\name\OneDrive - BB\Comparison\wrike - 656\*.xlsx")

If fname Like ("[05.]*") Then

Workbooks("05.2021 Monthly Comparison.xlsx").Worksheets("Summary").Range("B7:B12").Copy
Workbooks("Comparison Year to Date Summary 2021.xlsm").Worksheets("Summary").Range("E8").PasteSpecial Paste:=xlPasteValues

Workbooks("05.2021 Monthly Comparison.xlsx").Worksheets("Summary").Range("C15:C16").Copy
Workbooks("Comparison Year to Date Summary 2021.xlsm").Worksheets("Summary").Range("E17").PasteSpecial Paste:=xlPasteValues

If fname Like ("[06.]*") Then

Workbooks("06.2021 Monthly Comparison.xlsx").Worksheets("Summary").Range("B7:B12").Copy
Workbooks("Comparison Year to Date Summary 2021.xlsm").Worksheets("Summary").Range("F8").PasteSpecial Paste:=xlPasteValues

Workbooks("06.2021 Monthly Comparison.xlsx").Worksheets("Summary").Range("C15:C16").Copy
Workbooks("Comparison Year to Date Summary 2021.xlsm").Worksheets("Summary").Range("F17").PasteSpecial Paste:=xlPasteValues

End If
End If

End Sub

 

[mumps]

Will all 12 month files always exist? Do you want to copy the data from all existing month files or only certain existing month files?

 

[krehkop]

Will all 12 month files always exist? Do you want to copy the data from all existing month files or only certain existing month files?

Hello Mumps: No, currently only Jan - Jul exist. Thank you

 

[mumps]

OK. So would you want to copy data from all the Jan - July files? Would those files be the only files in that folder?

 

[krehkop]

Hello Mumps: No, currently only Jan - Jul exist. Thank you

So, only certain monthly files. Jan - Jun would already be in the Summary file. The Jul file is the latest and would be open, the macro would copy that data in and not error-out due to any other month.

 

[mumps]

I'm a little confused. Besides the Summary file, do you want to copy the data from all other open files?

 

[mumps]

Besides the Summary file, will there always be only one other monthly file open or can there be more than one monthly file open?

 

[krehkop]

I'm a little confused. Besides the Summary file, do you want to copy the data from all other open files?

Each month a new file will be added. In this example, July's file was the most recent. There are now 7 files (Jan - Jul) plus the Summary file. I'd like for the macro to copy Jul's data into the Summary file b/c in theory, Jan - Jun would have already been copied over. Jul's file would need to be open, b/c I don't know of another way to do it, in order for the macro to run successfully. I'm not married to this logic, it's just the path I started down. In summary, the data from the Jan - Jul files needs to be in the Summary and not have the macro error-out due to future months files which don't exist or prior months which may not be open.

 

[mumps]

So there would be only one monthly file open at any one time. Is this correct? Also, it looks like the column in the Summary sheet into which you paste the data, depends on the month being copied. Based on the code you posted, the paste column would correspond to the month, so Jan would be pasted into column A, Feb into column B, Mar into column C, etc. Is this correct?

 

[krehkop]

So there would be only one monthly file open at any one time. Is this correct? Also, it looks like the column in the Summary sheet into which you paste the data, depends on the month being copied. Based on the code you posted, the paste column would correspond to the month, so Jan would be pasted into column A, Feb into column B, Mar into column C, etc. Is this correct?

Yes, that is correct. Thank you