Different number of rows is being copied

[ExcelPupper]

Hello, as you can see from the code below I have set the last row of "column A" as the last row of data to be copied. To be more specific, column A has 288 rows while column D has 700 rows because it contains formula that refers to column C of the workbook where the data are being copied from. However, I'm having problem on pasting needed data on my summary workbook because Column D hast 288 rows but Column F contains 700 rows same as number of rows from workbook of raw data.

Sub Test1()
    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("E" & .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
    
    Workbooks(MyFile).Close SaveChanges:=False
        
End Sub

 

[NdNoviceHlp]

Hi again. Seems like similar code to your previous request which I see you haven't updated Just change the lastrow line...

lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A2:A" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("D2:D" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1).PasteSpecial xlPasteValues

HTH. Stay safe. Dave

 

[ExcelPupper]

Hi again. Seems like similar code to your previous request which I see you haven't updated Just change the lastrow line...

lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("A2:A" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("D2:D" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1).PasteSpecial xlPasteValues

HTH. Stay safe. Dave

Hello. I don't understand the code you provided, why does it have 2 variable for lastrow?
What I needed to have is to copy same number of rows for both column D & F. Is it possible despite the fact that the number of rows per column of raw data are not similar?
Btw, stay safe too!

 

[NdNoviceHlp]

You were copying "A" & "D" before and it seemed like U wanted to copy 288 rows from "A" and 700 from "D". If column "D" determines the number of rows for both "D" and "F" then ….

lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("D2:D" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("F2:F" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1).PasteSpecial xlPasteValues

Dave

 

[ExcelPupper]

You were copying "A" & "D" before and it seemed like U wanted to copy 288 rows from "A" and 700 from "D". If column "D" determines the number of rows for both "D" and "F" then ….

lastrow = .Range("D" & .Rows.Count).End(xlUp).Row
.Range("D2:D" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "D").End(xlUp).Offset(1).PasteSpecial xlPasteValues
.Range("F2:F" & lastrow).Copy
desWS.Cells(desWS.Rows.Count, "F").End(xlUp).Offset(1).PasteSpecial xlPasteValues

Dave

May I just ask, in relation to my question, I tried using this excel formula ROW(OFFSET(C1,COUNTA(C:C)-1,0)) to find the last row of my column C but upon checking the lastrow, it contains no data nor it contains space. How is this possible? Thanks!

 

[NdNoviceHlp]

That is a worksheet formula (probably not that easily adapted to VBA) which counts the rows with data in them in column "C". It would work for lastrow if all the rows in "C" had data in them. Dave