How to avoid copying data from empty source workbook

ExcelPupper

Board Regular
Joined
Mar 2, 2020
Messages
101
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,161
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
If lastrow <> 1 then
'copy/paste stuff
End If
 

Watch MrExcel Video

Forum statistics

Threads
1,119,114
Messages
5,576,188
Members
412,703
Latest member
sainayzawhtwe
Top