Optimizing code to copy data from one workbook and paste in another

Vbanoob98

Board Regular
Joined
Sep 13, 2019
Messages
118
Hello everyone,

Here is the code I have so far:

Sub ValuePaste()
Dim loLastRow as Long

ThisWorkbook.ActiveSheet.Range("E7").Copy With
Workbooks("name.xlsx").Worksheets("fees")
loLastRow = .Cells(Rows.Count, 6).End(xlUp).Row + 1
.Range("F" & loLastRow).PasteSpecial Paste:=xlPasteValues
End With

Dim ALastRow as Long

ThisWorkbook.ActiveSheet.Range("J7").Copy With
Workbooks("name.xlsx").Worksheets("fees")
loLastRow = .Cells(Rows.Count, 7).End(xlUp).Row + 1
.Range("A" & loLastRow).PasteSpecial :=xlPasteValues
End With

It basically copies a cell from one workbook and pastes in the first line of another. This repeats 7 more times so everything is in one row. Then we you run the code with another workbook and the data will go into the next row and so on.

Is there a way to make the code shorter or a better way to do it?

Thanks!
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,493
Office Version
  1. 2010
Platform
  1. Windows
it is much more efficient to load all of the data from your active sheet into a variant array asnd then write it back to the "fees" worksheet in one go
this code does exactly what your code above does:
Code:
inarr = Range("A7:J7")
With Workbooks("name.xlsx").Worksheets("fees")
loLastRow = .Cells(Rows.Count, 6).End(xlUp).Row + 1
.Range("F" & loLastRow) = inarr(1, 5) ' this is cell E7
loLastRow = .Cells(Rows.Count, 7).End(xlUp).Row + 1
.Range("A" & loLastRow) = inarr(1, 10)
End With
However you code seems quite strange because in the second section you check how where the last bit of data is in column 7 (G) but then paste the data into column A
If as is very common all the rows end in the same place it is not necessary to check for the last bit of data in separate columns once is Ok. It also opens up the possibility of writing the whole row at once using a variant array. this code does that:
Code:
Dim outarr(1 To 1, 1 To 6) As Variant
inarr = Range("A7:J7")
With Workbooks("name.xlsx").Worksheets("fees")
lolastrow = .Cells(Rows.Count, 6).End(xlUp).Row + 1
'set all cells to blanks
For i = 1 To 6
 outarr(1, i) = ""
Next i
outarr(1, 6) = inarr(1, 5)
outarr(1, 1) = inarr(1, 10)
.Range(.Cells(lolastrow, 1), .Cells(lolastrow, 6)) = outarr


End With
 
Last edited:

Vbanoob98

Board Regular
Joined
Sep 13, 2019
Messages
118
My coworkers where impressed with the code I had, but this is a true master at work.

How could I adapt your second formula so I can copy all of this cells from the first worksheet: E7 (Goes in column F), G43(A), C2(B), C3(D), G3(E), E9(G), E11(H),J23(i), G57(J) C51(L) and E51(M)?

Is it as simple as changing the range?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,521
Messages
5,548,544
Members
410,847
Latest member
shaun32
Top