[Help] Copying data from one sheet to the last row of another

[BennyWhatever]

Hello,

I've been searching everywhere (particularly on these forums and Microsoft's help site), and I haven't found a solution that has completely worked yet. I'm experienced in Excel but still a little new to VBA.

What I'm trying to do is copy only the cells that have integers in them from range B3:D20 on sheet "Sales", and past them to the row following the last used row in a different sheet "Invoices". Range E3:I20 all use Vlookup so they don't need to be copied over.

I'm currently using this code, but it's pasting over the formatting (which I don't want) and it's not pasting following the last used row - just going over the top of whatever is currently there.

Private Sub CommandButton5_Click()    Dim rng As Range
    Set rng = Worksheets("Sales").Range("B3:D20")
    rng.Copy
    With Worksheets("Invoices")
        Dim lr As Long: lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
        .Cells(lr, 2).PasteSpecial xlAll
    End With
    
End Sub

Here is the Sales sheet.


Here's the Invoices sheet.

 

[DutchDiggy]

Hi, this could work I guess. Someone really smart will have a greater way to solve it... but untill further notice:

Private Sub CommandButton5_Click()
Dim rng As Range
    Set rng = Worksheets("Sales").Range("B3:D20")
    rng.Copy
    Sheets("Invoices").Select
       Range("B14400").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sales").Select
    
End Sub

 

[BennyWhatever]

While it looks like a nice makeshift, I'm wondering what the Range("B14400").Select is supposed to be. It's giving me a runtime error and I can see why. What should I be trying to select there?

 

[DutchDiggy]

Hi BennyWhatever,

It's suppost to select a cell far away down and then go up again with xlUP. Maybe your sheet isn't big enough. You can replace it with

   Range("B1").Select
    Selection.End(xlDown).Select

Only exception is that before the first entry-copying you'll have to enter in B2 of the Invoices-sheet a 'dummy'. Enter REMOVE in B2. (text)

From the first time you've added data to invoices by running the Macro, you'll never have to process that. But because XLdown with only an entry in B1 will select the far bottom row in the sheet.

When you've added the first data, you'll simply remove the row which has the 'Remove' text in it. After that... just shoot your data in like a madman...

 

[BennyWhatever]

Figured it out! I revised it slightly, because it was confused on the Ranged line.
Here's the code now. Thanks soooooo much for the help, DutchDiggy.

Private Sub CommandButton5_Click()Dim rng As Range
    Set rng = Worksheets("Sales").Range("B3:D20")
    rng.Copy
    Sheets("Invoices").Activate
    ActiveSheet.Range("B14400").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sales").Select
    
End Sub

 

[DutchDiggy]

Figured it out! I revised it slightly, because it was confused on the Ranged line.
Here's the code now. Thanks soooooo much for the help, DutchDiggy.

! Great to be of your assistance! Thanks for the reply! Bottoms up!

 

[L. Howard]

Try this.
There are few reasons to ever have to Select cells or ranges to minupliate them.

Regards,
Howard

Option Explicit

Sub VariableRangeCopy()
    Dim wb As Workbook
    Dim src As Worksheet      'sheet you copy from  (source)
    Dim tgt As Worksheet      'sheet you copy to  (target)
    Dim scrLastRow As Long   'the last row on sheet from
    Dim tgtLastRow As Long   'the last row on sheet to

    Set wb = ThisWorkbook
    
    Set src = wb.Sheets("Sales")
    Set tgt = wb.Sheets("Invoices")
     
    scrLastRow = src.Cells(Rows.Count, "B").End(xlUp).Row + 1
    tgtLastRow = tgt.Cells(Rows.Count, "B").End(xlUp).Row + 1
    
    src.Range("B2: D" & scrLastRow).Copy tgt.Range("B" & tgtLastRow)
End Sub