Copy and Insert Rows - Use Dynamic Range and Paste to First Blank

[sbmaurer]

Hello,

Am currently an intern and a VBA newbie. Looking to write a code that inserts rows from one master template sheet and enters them into another interview sheet.

The template may change and this process needs to be repeated a certain number of times. (this number is set of certain cells that are filled in and hope to make the copy paste loop in the future)

For now what I looking for help on is.

Selecting the rows and inserting them with formats and formulas into the first blank cell in a range.

Here is my code, it is not working on the very last line?

Sub Copy_And_InsertRows_From_One_Sheet_To_Another()LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1


With Worksheets("Score_Template")
Range("B10").Select
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
End With
Worksheets("Interview_Card").Range("B" & LastRow).Select
Selection.Insert


End Sub

I can post files if anyone can help.

Thanks

 

[mumps]

Perhaps it would help if you could post your file.

 

[sbmaurer]

Hello,

Apparently I can not attach a file? Can anyone tell me how to post a file? Some further information is below.

Want to take the information from range B10:M18 (this range can change) on "Score_Template." and insert the rows along with their formulas and formats into the first blank under B10 on "Interview_Card."

Code below, appreciate any help!

Sub Copy_And_InsertRows_From_One_Sheet_To_Another()


LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1


With Worksheets("Score_Template")


Range("B10").Select
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).EntireRow.Select
Application.CutCopyMode = False
Selection.Copy
End With


Worksheets("Interview_Card").Range("B" & LastRow).Select
Selection.Insert


End Sub

Have been having issues:
1) inserting the entire rows, have been able to paste the data, but want to insert so it doesn't paste over values below.
2) copy the formulas, if I record a macro and copy and insert it keeps the formulas in line, such as on "Score_Template" when I copy and paste from range B10:M18 cell H11 is set to =G11. If I use cose to paste formulas on the new sheet it keeps "=G11", when I use copy and paste with a macro recorder it changes to "=G19" which is its new position in the range B19:L28.
3)

 

[mumps]

You could upload your file to a free service at www.box.com or other similar website. After you register and upload your file, you will be given a link to that file. Post the link in this forum and then your file can be downloaded.

 

[sbmaurer]

File location:

https://www.box.com/s/p4hahk7w9clqp15ik7we

Let me know if it works!

 

[mumps]

Try this code. In your file, you have changed the height of most of the rows. You may have to re-adjust the height. This macro will copy the range B10 to M18 below the existing data in the Interview_Card sheet each time you run it. It will also copy the formulas and adjust the ranges automatically. If you want more space between the copies, just change the Offset number. At present, it will put 6 blanks rows in between. Hopefully, this is what you were looking for.

Sub CopyData()
    Dim bottomK As Integer
    bottomK = Sheets("Score_Template").Range("K" & Rows.Count)
    Sheets("Score_Template").Range("B10:M18").Copy Sheets("Interview_Card").Cells(Rows.Count, "B").End(xlUp).Offset(6, 0)
End Sub