VBA Macros to insert a row at the end of a table, then paste values of previously copied lines in a specific range of the new row

Question

I'm looking for help on a VBA Macros. This is my current code. Where the **** are I need some code to insert a row at the end of the table on the active worksheet, and then paste the values copied in the above code with the range of ("E1:R8") into the range (E?:R?") of the newly created row.

Sub Workbook()
    Dim wb As Workbook
    Set wb = Workbooks.Add

    ThisWorkbook.Sheets("RFP Form").Copy Before:=wb.Sheets(1)
    ThisWorkbook.Sheets("DataHelperSheet").Copy After:=wb.Sheets(1)
    Application.DisplayAlerts = False

    wb.SaveAs "Z:\Temp\test3.xlsx"

    Application.DisplayAlerts = True

    ActiveWorkbook.SaveAs FileName:="Z:\Temp\" & Range("I1").Value

    Worksheets("DataHelperSheet").Activate
    Range("E1:R8").Select
    Selection.Copy

    Workbooks("Proposal Quote Master List(LB).xlsm").Activate
    Worksheets("Master List").Activate
    '***

    Range("E1:R298").PasteSpecial Paste:=xlPasteValues
End Sub

You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Oct 20 '20 at 05:57

MNDevelopments · Answer 1 · 2020-10-20T20:47:32.523

0

You can find the last row in a table by using this after you've selected the first cell in the table: (Try it like this)

Range("E1").Columns.End(xlDown).Offset(1,0).EntireRow.Insert

Or you can paste the data with:

.PasteSpecial Paste:=xlPasteFormats

You should look at ExcelCampus and this stack overflow question How to insert copied cells instead of paste

Above should give you all you need to do what you are asking for.

edited Oct 20 '20 at 20:47

answered Oct 19 '20 at 15:41

MNDevelopments

199
12

Thank you, but how would I paste the data that was copied? – lbradbury1013 Oct 19 '20 at 18:28
See my edit - please mark the question as solved if it helps. – MNDevelopments Oct 20 '20 at 20:47

score 0 · Answer 2 · answered Oct 20 '20 at 06:17

Don't name your sub Workbook it is a reserved word because VBA uses this for worbook objects (see at Dim wb As Workbook) and this can be very confusing for humans and VBA.
Don't mix ThisWorkbook and ActiveWorkbook and avoid using Activate, ActiveWorkbook and .Select. ThisWorkbook is the workbook the code is written in (it never changes. ActiveWorkbook is the workbook that has focus (is on top) and it can easily change by a single mouse click (therefore it is not a very reliable reference). Instead always try to use a fix reference like you did with wb.SaveAs.
Make sure all your Range objects have a workbook and worksheet specified. If you write Range("I1").Value VBA does not definitely know which workbook or worksheet you mean. It guesses you mean the active ones. But again this is not very reliable because this can change by a single mouse click. Make sure you tell VBA exactly what you mean by using something like wb.Workbooks("Sheet1").Range("I1").Value so there is no room for VBA to start guessing.

Stop using .Select. Instead of

 Worksheets("DataHelperSheet").Activate
 Range("E1:R8").Select
 Selection.Copy

just write

 wb.Worksheets("DataHelperSheet").Range("E1:R8").Copy

again specify the workbook wb if working with more than one workbook.

To find the last used cell in column E for example use
```
 wsMasterList.Cells(wsMasterList.Rows.Count, "E").End(xlUp)
```
and use .Offset(RowOffset:=1) to move one row down to the next empty cell to paste at.

So you end up with something like:

Option Explicit

Public Sub CreateWorkbook()
    Dim wb As Workbook
    Set wb = Workbooks.Add

    ThisWorkbook.Sheets("RFP Form").Copy Before:=wb.Sheets(1)
    ThisWorkbook.Sheets("DataHelperSheet").Copy After:=wb.Sheets(1)
    
    Application.DisplayAlerts = False
    wb.SaveAs "Z:\Temp\test3.xlsx"
    Application.DisplayAlerts = True

    wb.SaveAs Filename:="Z:\Temp\" & wb.Workbooks("SPECIFY YOUR SHEET").Range("I1").Value '‹~~ specify sheet name

    wb.Worksheets("DataHelperSheet").Range("E1:R8").Copy
    
    Dim wsMasterList As Worksheet
    Set wsMasterList = Workbooks("Proposal Quote Master List(LB).xlsm").Worksheets("Master List")

    wsMasterList.Cells(wsMasterList.Rows.Count, "E").End(xlUp).Offset(RowOffset:=1).PasteSpecial Paste:=xlPasteValues
End Sub

VBA Macros to insert a row at the end of a table, then paste values of previously copied lines in a specific range of the new row

2 Answers2