How to avoid copying data from empty source workbook

ExcelPupper

Board Regular
Joined
Mar 2, 2020
Messages
112
Office Version
  1. 2019
Platform
  1. Windows
Hi, good day. I'm trying to make a macro for copying data from a workbook with 2 different sheets and pasting them on different summary workbook. However, I'm having problem on copying data from SemiOpt sheet because it doesn't always contain data and currently I end up copying the header for each column from this sheet because it does not have data as of now.

Code:
    Sub TestPerProcess()
   
    Application.ScreenUpdating = False
    Dim lastrow As Long, srcWB As Workbook, desWS As Worksheet
    Dim MyLoc As String, MyFile As String
    Dim wb As Workbook

    MyLoc = "C:\Users\ADMIN\Desktop\Samples\" & ThisWorkbook.Sheets("data").Range("A3").Value & "\AllProcess\Process1\"
    MyFile = MyLoc & ThisWorkbook.Sheets("data").Range("B5").Value

    Set wb = Workbooks.Open(MyFile)
        
    Set srcWB = wb
    Set desWS = ThisWorkbook.Sheets("Process1")
    With srcWB
        With .Sheets("Semi")
            lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
            .Range("A2:A" & lastrow).Copy
            desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Range("D2:D" & lastrow).Copy
            desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Range("G2:G" & lastrow).Copy
            desWS.Cells(desWS.Rows.Count, "G").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
        
        With .Sheets("SemiOpt")
            lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
            .Range("A2:A" & lastrow).Copy
            desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Range("D2:D" & lastrow).Copy
            desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Range("G2:G" & lastrow).Copy
            desWS.Cells(desWS.Rows.Count, "G").End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With


    End With
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    wb.Close SaveChanges:=False
    
End Sub
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,214,808
Messages
6,121,681
Members
449,048
Latest member
81jamesacct

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