Skip if workbook2 not open

[pwill]

Hi,can anyone help?

I have a macro that calls a sub and then copies the data to workbook2.
If workbook2 is not open I want to skip a certain part of the code so it doesn't error because workbook2 is closed.

does anyone know of a line of code I can add within the macro to skip a certain part and continue the macro until end sub

Any help would be appreciated

Regards

pwill

 

[Fluff]

Something like

   Dim Wbk As Workbook
   
   On Error Resume Next
   Set Wbk = Workbooks("Mybook.xlsm")
   On Error GoTo 0
   If Wbk Is Nothing Then
      MsgBox "not open"
   Else
      MsgBox "Open"
   End If

 

[pwill]

Thank you Fluff,

I will give this a try when I get home and let you know how I get on

Much appreciated

pwill

 

[pwill]

Something like

   Dim Wbk As Workbook
   
   On Error Resume Next
   Set Wbk = Workbooks("Mybook.xlsm")
   On Error GoTo 0
   If Wbk Is Nothing Then
      MsgBox "not open"
   Else
      MsgBox "Open"
   End If

Hi Fluff, I am trying incorporate your code into this macro, could you help?

I have marked where I need the macro to skip

Private Sub Updt_Click()

Dim wsUpDates As Worksheet: Set wsUpDates = Sheet01
Dim wsDates As Worksheet: Set wsDates = Sheet02
Dim lRowAS As Long
Dim cnt As Long
Dim dts As Long
Dim i As Long
    
    Application.ScreenUpdating = False
    
        Call Clr_Data
        
    dts = tbAddUpdts
        For i = 1 To dts
            Application.Run "UserForm_Archive.CommandButton0_Click"
        Next
    
        Call RowFilter
        
    'if workbook2 not open skip this step
        Call CopyData
        
    'continue Macro from here
    lRowAS = wsUpDates.Cells(Rows.Count, "AS").End(xlUp).Row
    
    cnt = Range("AP4").Value
        For i = 0 To cnt
    
            If wsDates.Range("AP4") = i Then
                wsDates.Range("AK1").Value = "Next Update"
                wsDates.Range("AK2").Value = wsUpDates.Range("AI" & lRowAS).Value
            End If
        Next
        
    'if workbook2 not open skip this step
    Workbooks("Update.xlsm").Sheets("Dates").Range("AK1:AK3").Copy Workbooks("workbook2.xlsm").Sheets("Dates").Range("AK1:AK3")
    
    'continue Macro from here
        With Archive
            .tbUpdts = wsDates.Range("AP4").Value
        End With
    
        Range("D2").Select
    
    Application.ScreenUpdating = True

End Sub

regards

pwill

 

[Fluff]

Try

Private Sub Updt_Click()

Dim Wbk As Workbook
Dim wsUpDates As Worksheet: Set wsUpDates = Sheet01
Dim wsDates As Worksheet: Set wsDates = Sheet02
Dim lRowAS As Long
Dim cnt As Long
Dim dts As Long
Dim i As Long
    
    Application.ScreenUpdating = False
    
        Call Clr_Data
        
    dts = tbAddUpdts
        For i = 1 To dts
            Application.Run "UserForm_Archive.CommandButton0_Click"
        Next
    
        Call RowFilter
        
    'if workbook2 not open skip this step
        Call Copydata
        
    'continue Macro from here
    lRowAS = wsUpDates.Cells(Rows.Count, "AS").End(xlUp).Row
    
    cnt = Range("AP4").Value
        For i = 0 To cnt
    
            If wsDates.Range("AP4") = i Then
                wsDates.Range("AK1").Value = "Next Update"
                wsDates.Range("AK2").Value = wsUpDates.Range("AI" & lRowAS).Value
            End If
        Next
        
    'if workbook2 not open skip this step
   On Error Resume Next
   Set Wbk = Workbooks("Update.xlsm")
   On Error GoTo 0
   If Not Wbk Is Nothing Then
      Wbk.Sheets("Dates").Range("AK1:AK3").Copy Workbooks("workbook2.xlsm").Sheets("Dates").Range("AK1:AK3")
   End If
    'continue Macro from here
        With Archive
            .tbUpdts = wsDates.Range("AP4").Value
        End With
    
        Range("D2").Select
    
    Application.ScreenUpdating = True

End Sub

 

[pwill]

Thanks Fluff for your reply,

Not sure whats going wrong but its going to debug with the sub

'Call Copydata'

and if I take that line out it still goes to debug at this line

Wbk.Sheets("Dates").Range("AK1:AK3").Copy Workbooks("workbook2.xlsm").Sheets("Dates").Range("AK1:AK3")

any thoughts why it does this?

pwill

ps I should mention I am keeping workbook2 closed for the purpose of trying this out

 

[pwill]

Just a thought, I am running this code from a UserForm, would that make any difference?

regards

 

[pwill]

Thanks Fluff for your reply,

Not sure whats going wrong but its going to debug with the sub

'Call Copydata'

and if I take that line out it still goes to debug at this line

Wbk.Sheets("Dates").Range("AK1:AK3").Copy Workbooks("workbook2.xlsm").Sheets("Dates").Range("AK1:AK3")

any thoughts why it does this?

pwill

ps I should mention I am keeping workbook2 closed for the purpose of trying this out

Thanks for your help Fluff, much apprieciated, it does what I need by just adding 'On Error Resume Next'

ie

Private Sub Updt_Click()

Dim Wbk As Workbook
Dim wsUpDates As Worksheet: Set wsUpDates = Sheet01
Dim wsDates As Worksheet: Set wsDates = Sheet02
Dim lRowAS As Long
Dim cnt As Long
Dim dts As Long
Dim i As Long
    
    Application.ScreenUpdating = False
    
        Call Clr_Data
        
    dts = tbAddUpdts
        For i = 1 To dts
            Application.Run "UserForm_Archive.CommandButton0_Click"
        Next
    
        Call RowFilter

On Error Resume Next        
    'if workbook2 not open skip this step
        Call Copydata
        
    'continue Macro from here
    lRowAS = wsUpDates.Cells(Rows.Count, "AS").End(xlUp).Row
    
    cnt = Range("AP4").Value
        For i = 0 To cnt
    
            If wsDates.Range("AP4") = i Then
                wsDates.Range("AK1").Value = "Next Update"
                wsDates.Range("AK2").Value = wsUpDates.Range("AI" & lRowAS).Value
            End If
        Next
        
     Set Wbk = Workbooks("Update.xlsm")
On Error Resume Next
    'if workbook2 not open skip this step
      Wbk.Sheets("Dates").Range("AK1:AK3").Copy Workbooks("workbook2.xlsm").Sheets("Dates").Range("AK1:AK3")

    'continue Macro from here
        With Archive
            .tbUpdts = wsDates.Range("AP4").Value
        End With
    
        Range("D2").Select
    
    Application.ScreenUpdating = True

End Sub

regards

pwill