Excel VBA Copy & Insert Cells Range

[ekfasy]

Hello,

I currently have VBA coding that is copying a variable range from one excel spreadsheet and pasting it into another.

Below is the code:

Sub MoveData()    Dim wb1 As Workbook, wb2 As Workbook
    Dim lr As Long
    Set wb1 = Workbooks("Copy of XYZ-ABCQuote-Testing") ' Change the name to match your workbook.
    Set wb2 = Workbooks("ABC-Quote To Customer-Testing") ' Change the name to match your workbook.
    With wb1.Worksheets("Quote") 'Change the name to match your worksheet.
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range(.Cells(21, 1), .Cells(lr, 1)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("C24") 'Change the name to match your worksheet.
        
        lr = .Cells(.Rows.Count, "B").End(xlUp).Row
        .Range(.Cells(21, 2), .Cells(lr, 2)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("D24") 'Change the name to match your worksheet.
        
        lr = .Cells(.Rows.Count, "C").End(xlUp).Row
        .Range(.Cells(21, 3), .Cells(lr, 3)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("B24") 'Change the name to match your worksheet.
        
        lr = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range(.Cells(21, 4), .Cells(lr, 4)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("E24") 'Change the name to match your worksheet.
        
        lr = .Cells(.Rows.Count, "E").End(xlUp).Row
        .Range(.Cells(21, 5), .Cells(lr, 5)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("F24") 'Change the name to match your worksheet.
        
    End With
     
End Sub

Does anyone know if it is possible to not just copy and paste these cells from wbk1 into wbk2, but instead to copy the cells from wbk1 and insert rows for those cells into wbk2. The number of rows will vary each time I run this macro, so it's important that the range it's being pasted into can be variable in the number of rows as well.

Any advice would be much appreciated!!!

Thanks

 

[skywriter]

You should post the links to the other forums, so every forum know it's posted on another forum.

 

[ekfasy]

Oops! I posted this question on two other forums, below are the links:

Excel VBA Copy & Insert Cells for a Variable Range

AND

Excel VBA Copy & Insert Cells Range