ExcelPupper
Board Regular
- Joined
- Mar 2, 2020
- Messages
- 112
- Office Version
- 2019
- Platform
- 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