Paste data from a sheet to another to the last row with variables

Question

Im kinda new with Excel macros so maybe this is a basic question but i have a sheet where i put data, print it and i have to put it on another sheet, i tried doing it without variables but it takes forever and its kinda messy, so im trying using them, my problem is that i cannot make it paste to the last blank cell, every time i use the macro, it overwrite everything, and i run out of ideas,

I tried using loops, accumulators, but i cant figure out how to paste everything to the last cell, and im not able to find a solution for this

Sub COPIAR()
'define variables
Dim fecha As Date
Dim arato As Variant
Dim direccion As String
Dim cuadrilla As String
Dim id As Variant
Dim material() As Variant
Dim cantidad() As Variant
Dim g As Long
Dim lastrow As Long
Dim ws As Worksheet
g = 1
Set ws = Sheets("CARGA")
'set the values
Worksheets("REMITO").Select
fecha = Range("K5").Value
arato = Range("K11").Value
direccion = Range("B9").Value
cuadrilla = Range("B16").Value
id = Range("K8").Value
Range("C28").Select
'copy till the last used cell
While ActiveCell.Value <> ""
g = g + 1
ActiveCell.Offset(1, 0).Select
Wend
Dim LR As Long
LR = Range("C" & Rows.Count).End(xlUp).Row
material() = Range("C28:C" & LR)
cantidad() = Range("M28:M" & LR)


'paste
Worksheets("CARGA").Select
Range("H2:H" & g).Value = material()
Range("A2:A" & g).Value = fecha
Range("B2:B" & g).Value = arato
Range("C2:C" & g).Value = direccion
Range("D2:D" & g).Value = cuadrilla
Range("F2:F" & g).Value = id
Range("I2:I" & g).Value = cantidad()
End Sub

This is my code, thanks in advance!

First of all, get rid of the entire `While ... Wend` loop. See [find last used cell](https://stackoverflow.com/questions/11169445/find-last-used-cell-in-excel-vba), which is the approach you are correctly taking with `LR`. Also `Range("H2:H" & g)` rewrites starting from row 2... is that what you want? — BigBen, Jul 11 '23 at 13:46
i used the While ... Wend as a counter to look every material and count the rows, thats what g does, sorry for not explaining it, i get rid of it and now it pastes in a random row; i used Range("H2:H" & g) because i dont really know other way, and every other way i found didnt really do anything — Navvvvx__, Jul 11 '23 at 14:02

Tim Williams · Answer 1 · 2023-07-11T17:34:32.667

Try something like this:

Sub COPIAR()

    Dim material As Variant, cantidad As Variant
    Dim g As Long, lastrow As Long
    Dim wsCarg As Worksheet, wsRem As Worksheet, wb As Workbook
   
    Set wb = ThisWorkbook 'for example
    Set wsCarg = wb.Worksheets("CARGA")
    Set wsRem = wb.Worksheets("REMITO")
    
    lastrow = wsRem.Cells(Rows.count, "C").End(xlUp).row
    material = wsRem.Range("C28:C" & lastrow).Value
    cantidad = wsRem.Range("M28:M" & lastrow).Value
    g = UBound(material, 1) 'number of rows of data
    
    lastrow = wsCarg.Cells(Rows.Count,"A").End(xlUp).Row 'use a column which will always have a value
    With wsCarg.Rows(lastrow + 1).Resize(g)
        .Columns("A").Value = wsRem.Range("K5").Value   'fecha
        .Columns("B").Value = wsRem.Range("K11").Value  'arato
        .Columns("C").Value = wsRem.Range("B9").Value   'direccion
        .Columns("D").Value = wsRem.Range("B16").Value  'cuadrilla
        .Columns("F").Value = wsRem.Range("K8").Value   'id
        .Columns("H").Value = material()
        .Columns("I").Value = cantidad()
    End With

End Sub

Getting the value of lastrow relies on assuming there's nothing else below your data.

It keeps overwriting itself, but thank you so much, is much more simple and i think i understand it, the only thing i dont know how to do is how to make it paste to the last row — Navvvvx__, Jul 11 '23 at 17:24
See edit above to start copying to the next empty row on "CARGA" — Tim Williams, Jul 11 '23 at 17:35
Thank you so much, it worked! i think the error was that i was using tables, and for some reason it started in the last row every time — Navvvvx__, Jul 11 '23 at 17:50

Paste data from a sheet to another to the last row with variables

1 Answers1