If Not IsEmpty Error

[Glasgowsmile]

Hello,

Some context for this issue. I am trying to move over this whole VBA code to my personal book that is stored in XLSTART and run the macros off that moving forward instead of having them in the reports themself. The code below works fine in the document on its own but doesn't work when I get to the If Not IsEmpty section on the Personal book macro.

I figure I need to target the excel workbooks more directly since I'm using the personal book, which I don't fully know how to do. I tried adding the below code to the Empty formula but it doesn't work. Any help or insight would be greatly appreciated!

If Not IsEmpty(MBook.Sheet2.Range("A6").Value) Then

Sub Import()
   Application.DisplayAlerts = False
  
   Dim MBook As Workbook
   Dim IBook As Workbook
  
   Dim sh As Worksheet, ws As Worksheet
   Dim rg1 As Variant
   Dim r1 As Long, c1 As Long

   Set MBook = ActiveWorkbook
   Set sh = MBook.Sheets(1)
  
   With Application.FileDialog(msoFileDialogOpen)
      .Filters.Clear
      .Filters.Add "Excel 2007-13", "*.xlsx; *.xls; *.xlsm; *.xlsa; *.csv"
      .AllowMultiSelect = False
      .Show
      If .SelectedItems.Count > 0 Then
         Workbooks.Open .SelectedItems(1)
         Set IBook = ActiveWorkbook
         Set ws = IBook.Sheets(1)
        
         If Not IsEmpty(Sheet2.Range("A6").Value) Then
               
                Sheet3.Range("A1:S400").ClearContents
               
                Sheet2.Range("A1:S400").Copy
                Sheet3.Range("A1").PasteSpecial xlPasteValues
               
                Sheet2.Range("A1:S400").ClearContents
               
            End If
        
         With ws
            rg1 = .Range("A1:S400").Value
        
            r1 = UBound(rg1): c1 = UBound(rg1, 2)

         End With
        
         With sh
        
         Sheet2.Range("A1").Resize(r1, c1).Value = rg1
        
         End With
       
         IBook.Close False
        End If
        End With
       
  Sheet1.Select
  Application.CutCopyMode = False
End Sub

 

[Fluff]

You can only use sheet codenames for the sheets in the workbook containing the code.
You can either use the actual sheet name, or loop through the sheets & set them to a variable like

   Dim Ws As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
   Dim MBook As Workbook
   
   Set MBook = ActiveWorkbook
   For Each Ws In MBook.Worksheets
      If Ws.CodeName = "Sheet2" Then
         Set Sh2 = Ws
      ElseIf Ws.CodeName = "Sheet3" Then
         Set Sh3 = Ws
      End If
   Next Ws

 

[Glasgowsmile]

You can only use sheet codenames for the sheets in the workbook containing the code.
You can either use the actual sheet name, or loop through the sheets & set them to a variable like

   Dim Ws As Worksheet, Sh2 As Worksheet, Sh3 As Worksheet
   Dim MBook As Workbook
  
   Set MBook = ActiveWorkbook
   For Each Ws In MBook.Worksheets
      If Ws.CodeName = "Sheet2" Then
         Set Sh2 = Ws
      ElseIf Ws.CodeName = "Sheet3" Then
         Set Sh3 = Ws
      End If
   Next Ws

Thanks,

That will help me with the rest of my macros I'm moving over.

 

[Fluff]

You're welcome & thanks for the feedback.