Excel VBA Copy & Insert Cells for a Variable Range

  • 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:


    Code
    [/COLOR]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[COLOR=#333333]



    [/COLOR]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 :)



    Also posted on:
    http://www.mrexcel.com/forum/e…y-insert-cells-range.html


    and


    http://www.excelforum.com/show…565&p=4394099#post4394099

  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    You haven't stated where you want the new rows to be inserted and for instance if it's row 5, do you want row 5 to stay where it is and the new rows to be inserted below row 5 or do you want row 5 to be pushed down by the new rows.

  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    Hi Bruce!


    I was trying not to bother you again on my other thread, but since you're here now.. :)


    I would like to insert rows before row 24 (new rows push down row 24) in wb2 (ABC-Quote to Customer-Testing)


    Thanks!

  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    I'm thinking when you set a workbook to a variable using code such as this.

    Code
    Set wb1 = Workbooks("Copy of XYZ-ABCQuote-Testing")


    That you need two things, the workbook must be open and you need to include the file extenstion .xls or .xlsx etc. whatever applies to your workbook.


    So if you have issues add the extension, making sure you use the right extension, the example below is just that, an example, if your workbook is not a .xlsm then don't use .xlsm, use the correct extension.

    Code
    Set wb1 = Workbooks("Copy of XYZ-ABCQuote-Testing.xlsm")



    Try this, but see my notes above, you may need to add the file extensions where you set the workbooks to variables. :cool:

  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    Bruce,


    I tried the code you provided and then tried the code with the file extension to the workbooks mentioned in the macro, but with both there is an error saying "subscript out of range" and does nothing. Any ideas?


    Thanks!

  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    On my computer I get that error in the following situations.


    1) The workbook is closed when I run the macro, the workbook must be open first.


    2) The workbook is open, but the name is misspelled. Extra spaces or spaces where there are none are common errors in the misspelling of workbook names and worksheet names, but it just may be a typo in the name. I tend to type "text" when I mean to type "test", I would suggest you go to your workbook files and choose rename so that you can copy the name and then paste it into your code to make sure there's no issues.


    3) The extension is left off or is incorrect.
    Example: The file name is "Test.xls" and it's entered as "Test" or "Test.xlsx", it has to be correct.


    There may be others, but these are three I've tested myself.

  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    I went through and double checked everything, including extensions, spelling, and making sure the file is open. It looks really wonky though when I run it now. It inserts a bunch of lines and then only copy and pastes in one column of data, then inserts more lines...

  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    I'm making an assumption based on the way you chose to write your original code.


    Why, if in your original code you are pasting all the data into row 24 are you checking the last row in each column?


    Just take all the data at once and paste it into row 24.


    This is my confusion as to what you are doing.


    I thought you were going to modify the code and paste it all into one column on top of itself.


    Do you want to paste all the data into columns A through E and you want to make sure enough rows are added to accommodate the longest column of data?

  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    Make sure the code for the workbooks is right so you don't get the same error as before. :cool:


  • Re: Excel VBA Copy & Insert Cells for a Variable Range


    Bruce,


    I just realized one thing... wb1 has the columns in a different order than wb2, so my first set of coding that you helped me with on another thread would copy each column from wb1 separately and then paste it into the correct column in wb2. Code Below:



    But the new code you just provided correctly copies the data from wb1 and inserts the appropriate amount of rows into wb2 when it pastes the data in, but it doesn't account for the fact that the columns are in a different order..


    For example in wb1 the columns are in this order:


    (column A) Product Code
    (column B) Product Description
    (column C) Quantity
    (column D) List(Unit)
    (column E) List Price


    In wb2, however, the columns are in the following order:
    (column B) Quantity
    (column C) Item Number <<same thing as product code>>
    (column D) Product Description
    (column E) Unit List Price
    (column F) Ext List Price


    So the columns from the different workbooks line up like this:
    [TABLE="width: 500"]

    [tr]


    [td]

    wb1

    [/td]


    [td]

    wb2

    [/td]


    [/tr]


    [tr]


    [td]

    column A

    [/td]


    [td]

    Column C

    [/td]


    [/tr]


    [tr]


    [td]

    Column B

    [/td]


    [td]

    Column D

    [/td]


    [/tr]


    [tr]


    [td]

    Column C

    [/td]


    [td]

    Column B

    [/td]


    [/tr]


    [tr]


    [td]

    Column D

    [/td]


    [td]

    Column E

    [/td]


    [/tr]


    [tr]


    [td]

    Column E

    [/td]


    [td]

    Column F

    [/td]


    [/tr]


    [/TABLE]



    Any ideas on how to mesh the two codes together so that it inserts the rows from the code you just gave me, but also keeps the columns lined up properly like in my previous code?

  • Re: Excel VBA Copy &amp; Insert Cells for a Variable Range


    Try this. :cool:

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!