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

[Vbanoob98]

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!

 

[offthelip]

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:

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:

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

 

[Vbanoob98]

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?

 

[Vbanoob98]

It was as simple as changing the range. Thanks so much!!