VBA: IF THEN, skip if file not open

krehkop

Board Regular
Joined
Jul 6, 2007
Messages
131
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
This macro assumes that only the Summary file and one monthly file will open and that the data for all previous months already exists in the Summary file.
VBA Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, wb As Workbook, lcol As Long
    Set desWS = ThisWorkbook.Sheets("Summary")
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            With wb.Sheets("Summary")
                .Range("B7:B12").Copy
                desWS.Cells(8, desWS.Cells(8, desWS.Columns.Count).End(xlToLeft).Column + 1).PasteSpecial xlPasteValues
                .Range("C15:C16").Copy
                desWS.Cells(17, desWS.Cells(17, desWS.Columns.Count).End(xlToLeft).Column + 1).PasteSpecial xlPasteValues
            End With
        End If
    Next wb
    Application.ScreenUpdating = True
End Sub
 
Solution

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

krehkop

Board Regular
Joined
Jul 6, 2007
Messages
131
Update: I did some more Googling and the following is working. If the file is open it updates accordingly, if not then it does nothing and there are no errors (which is good). The only downside is that the year will have to be manually updated annually. Is there a way, for example, to somehow incorporate a 'LIKE' re: xRet = IsWorkBookOpen("02.2021 Monthly Comparison.xlsx") and Workbooks("02.2021 Monthly Comparison.xlsx").Worksheets("Summary").Range("B7:B12").Copy? If I could reference something like ("[02.]*") that would be ideal.

Function IsWorkBookOpen(Name As String) As Boolean
Dim xWb As Workbook
On Error Resume Next
Set xWb = Application.Workbooks.Item(Name)
IsWorkBookOpen = (Not xWb Is Nothing)
fname = Dir("C:\Users\kr\OneDrive - BB\Comparison\wrike - 656\*.xlsx")
End Function
Sub xxxxx()
Dim xRet As Boolean
'********************************************************************************************************************************
xRet = IsWorkBookOpen("02.2021 Monthly Comparison.xlsx")

If xRet Then

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

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

End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("03.2021 Monthly Comparison.xlsx")

If xRet Then

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

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

End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("04.2021 Monthly Comparison.xlsx")

If xRet Then

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

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

End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("05.2021 Monthly Comparison.xlsx")
If xRet 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

End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("06.2021 Monthly Comparison.xlsx")
If xRet 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
'********************************************************************************************************************************
xRet = IsWorkBookOpen("07.2021 Monthly Comparison.xlsx")
If xRet Then

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

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

End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("08.2021 Monthly Comparison.xlsx")
If xRet Then

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

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

End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("09.2021 Monthly Comparison.xlsx")
If xRet Then

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

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

End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("10.2021 Monthly Comparison.xlsx")
If xRet Then

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

Workbooks("10.2021 Monthly Comparison.xlsx").Worksheets("Summary").Range("C15:C16").Copy
Workbooks("Comparison Year to Date Summary 2021.xlsm").Worksheets("Summary").Range("J17").PasteSpecial Paste:=xlPasteValues
End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("11.2021 Monthly Comparison.xlsx")
If xRet Then

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

Workbooks("11.2021 Monthly Comparison.xlsx").Worksheets("Summary").Range("C15:C16").Copy
Workbooks("Comparison Year to Date Summary 2021.xlsm").Worksheets("Summary").Range("K17").PasteSpecial Paste:=xlPasteValues
End If
'********************************************************************************************************************************
xRet = IsWorkBookOpen("12.2021 Monthly Comparison.xlsx")
If xRet Then

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

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

'Else
'MsgBox "The file is not open", vbInformation, "Please Note:"
End If

End Sub
 

krehkop

Board Regular
Joined
Jul 6, 2007
Messages
131
This macro assumes that only the Summary file and one monthly file will open and that the data for all previous months already exists in the Summary file.
VBA Code:
Sub CopyData()
    Application.ScreenUpdating = False
    Dim desWS As Worksheet, wb As Workbook, lcol As Long
    Set desWS = ThisWorkbook.Sheets("Summary")
    For Each wb In Workbooks
        If wb.Name <> ThisWorkbook.Name Then
            With wb.Sheets("Summary")
                .Range("B7:B12").Copy
                desWS.Cells(8, desWS.Cells(8, desWS.Columns.Count).End(xlToLeft).Column + 1).PasteSpecial xlPasteValues
                .Range("C15:C16").Copy
                desWS.Cells(17, desWS.Cells(17, desWS.Columns.Count).End(xlToLeft).Column + 1).PasteSpecial xlPasteValues
            End With
        End If
    Next wb
    Application.ScreenUpdating = True
End Sub
 

krehkop

Board Regular
Joined
Jul 6, 2007
Messages
131
Thank you Mumps, that's very clean! I will check that out and let you know how it works.
 

krehkop

Board Regular
Joined
Jul 6, 2007
Messages
131
Mumps, using my Feb source file as the example, the 2nd part of the query (which references ".Range("C15:C16").Copy") pasted the results in column N instead of column B. The first part worked perfectly, the results were pasted in column B. I think I should be able to make some adjustments to get this to work. Thanks again!
 

Forum statistics

Threads
1,148,181
Messages
5,745,204
Members
423,933
Latest member
ankushmukherjee

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
Top