VBA: IF THEN, skip if file not open

K

krehkop

Board Regular
Joined
Jul 6, 2007
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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
 
Sort by date Sort by votes
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
 
Upvote 0
Solution

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
mumps said:
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
Click to expand...
 
Upvote 0
Thank you Mumps, that's very clean! I will check that out and let you know how it works.
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Making code easier to work with at start of new tax year
Replies
2
Views
174
ipbr21054
I
I
  • Solved
Set value for range being copied over with VBA
2
Replies
14
Views
729
ItalianPlatinum
I
R
  • Question Question
Compose Email VBA Help
Replies
1
Views
121
Logit
Logit
Z
  • Solved
Run-time error 9
2
Replies
12
Views
211
zerofeng
Z
B
  • Question Question
Copy and paste from one worksheet into a new workbook and save to a particular directory
Replies
3
Views
719
Joe4
Joe4

Forum statistics

Threads
1,224,587
Messages
6,179,735
Members
452,939
Latest member
WCrawford

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