If Not IsEmpty Error

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
176
Office Version
  1. 365
Platform
  1. Windows
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!

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

VBA Code:
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
 
Last edited:

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
   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
 
Solution

Glasgowsmile

Board Regular
Joined
Apr 14, 2018
Messages
176
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
   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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,627
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,143,638
Messages
5,719,977
Members
422,253
Latest member
frankie2016tata

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