Different number of rows is being copied

ExcelPupper

Board Regular
Joined
Mar 2, 2020
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,666
Hi again. Seems like similar code to your previous request which I see you haven't updated :) Just change the lastrow line...
Code:
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

Board Regular
Joined
Mar 2, 2020
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
Hi again. Seems like similar code to your previous request which I see you haven't updated :) Just change the lastrow line...
Code:
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!
 
Last edited:

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,666
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 ….
Code:
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

Board Regular
Joined
Mar 2, 2020
Messages
101
Office Version
  1. 2019
Platform
  1. Windows
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 ….
Code:
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

Well-known Member
Joined
Nov 9, 2002
Messages
2,666
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,309
Messages
5,571,477
Members
412,395
Latest member
nielsvanlit
Top