ExcelPupper
Board Regular
- Joined
- Mar 2, 2020
- Messages
- 112
- Office Version
- 2019
- Platform
- Windows
Hello. To begin with, when I ran this code earlier, I noticed that the last row of my data contained the column headers of "ProcessX" sheet and as I checked this sheet (ProcessX) it does not contain any data for now.
My question is what modifications should I do for this code to avoid copying from sheets that does not contain data? Many thanks.
My question is what modifications should I do for this code to avoid copying from sheets that does not contain data? Many thanks.
VBA Code:
Sub TestProcess()
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\TestFolder\" & ThisWorkbook.Sheets("Sample").Range("A1").Value & "\TestData\"
MyFile = MyLoc & ThisWorkbook.Sheets("data").Range("B5").Value
Set wb = Workbooks.Open(MyFile)
Set srcWB = wb
Set desWS = ThisWorkbook.Sheets("ProcessTree")
With srcWB
With .Sheets("Process1")
lastrow = .Range("E" & .Rows.Count).End(xlUp).Row
.Range("A2:A" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("E2:E" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "E").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("F2:F" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("I2:I" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "G").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("O2:O" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "J").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("D2:D" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "K").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
With .Sheets("Process2")
lastrow = .Range("C" & .Rows.Count).End(xlUp).Row
.Range("A2:A" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("C2:C" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "E").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
.Range("M2:M" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "J").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("H2:H" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "K").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
With .Sheets("ProcessX")
lastrow = .Range("C" & .Rows.Count).End(xlUp).Row
.Range("A2:A" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("C2:C" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "E").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
.Range("M2:M" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "J").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("H2:H" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "K").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End With
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
wb.Close SaveChanges:=False
End Sub