Excel VBA Copy & Insert Cells for a Variable Range

ekfasy · May 23rd 2016

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

skywriter · May 24th 2016

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.

ekfasy · May 24th 2016

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!

skywriter · May 24th 2016

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:

Code

Sub MoveData()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim lr As Long
    Dim rCount 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
        rCount = lr - 21
        wb2.Worksheets("ABC Quote").Rows("24:" & CStr(24 + rCount)).EntireRow.Insert
        .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
        rCount = lr - 21
        wb2.Worksheets("ABC Quote").Rows("24:" & CStr(24 + rCount)).EntireRow.Insert
        wb2.Worksheets("ABC Quote").Range ("D24")
        .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
        rCount = lr - 21
        wb2.Worksheets("ABC Quote").Rows("24:" & CStr(24 + rCount)).EntireRow.Insert
        .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
        rCount = lr - 21
        wb2.Worksheets("ABC Quote").Rows("24:" & CStr(24 + rCount)).EntireRow.Insert
        .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
        rCount = lr - 21
        wb2.Worksheets("ABC Quote").Rows("24:" & CStr(24 + rCount)).EntireRow.Insert
        .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

Display More

ekfasy · May 25th 2016

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!

skywriter · May 25th 2016

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.

ekfasy · May 25th 2016

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...

skywriter · May 25th 2016

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?

skywriter · May 25th 2016

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:

Code

Sub MoveData()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim lr As Long
    Dim rCount 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 = .Columns("A:E").Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
           SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
            
        rCount = lr - 21
        wb2.Worksheets("ABC Quote").Rows("24:" & CStr(24 + rCount)).EntireRow.Insert
        .Range(.Cells(21, 1), .Cells(lr, 5)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("A24") 'Change the name to match your worksheet.
        
    End With
     
End Sub

Display More

ekfasy · May 25th 2016

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

You are the absolute best!

Thank you so much. It works perfect now

skywriter · May 25th 2016

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

My pleasure, thanks for the feedback. :cool:

ekfasy · May 25th 2016

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:

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

Display More

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?

skywriter · May 25th 2016

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

Try this. :cool:

Code

Sub MoveData()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim lr As Long, rCount 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 = .Columns("A:E").Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
        rCount = lr - 21
        wb2.Worksheets("ABC Quote").Rows("24:" & CStr(24 + rCount)).EntireRow.Insert
        .Range(.Cells(21, 1), .Cells(lr, 1)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("C24") 'Change the name to match your worksheet.
         
        .Range(.Cells(21, 2), .Cells(lr, 2)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("D24") 'Change the name to match your worksheet.
         
        .Range(.Cells(21, 3), .Cells(lr, 3)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("B24") 'Change the name to match your worksheet.
        
        .Range(.Cells(21, 4), .Cells(lr, 5)).Copy _
        Destination:=wb2.Worksheets("ABC Quote").Range("E24") 'Change the name to match your worksheet.
     
    End With
     
End Sub

Display More

ekfasy · May 25th 2016

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

You're a genius! It works EXACTLY the way I need it to now.

Thank you so much

skywriter · May 25th 2016

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

My pleasure, thanks for the feedback. :cool:

Excel VBA Copy & Insert Cells for a Variable Range

Participate now!

Share