Excel 2010, Need to referance a defined name in a macro

[BOBBEY]

Trying to write a macro that takes a group of cells from a sheet named "Master" and be able to run the macro to insert that group of cells at the first blank row on sheet 2. Then if ran again inserts those cells again at the first blank row. I named the group of cells on sheet "Master" as New_Job. They are A1:Q15.

Here is the code I wrote but errors on the Range("New_Job").Select

Sub newend()
'
' newend Macro
Range("New_Job").Select
Selection.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=6
End Sub

Not sure if this is the way to go but after I get this to work I need to copy sheet 2 to a new sheet and have the macro work on that sheet as well. Inserting those same cells to the bottom from sheet "Master"

 

[BOBBEY]

Found an issue. When I delete a group of cells from the middle of my page and run the macro it puts the new one at the bottom again instead of at the first blank. Is there a way to change the macro to always insert the new block of cells at the first blank row from the top?

 

[MARK858]

Try

Sub newend()
    Dim New_Job As String
    Sheets("Master").Range("New_Job").EntireRow.Copy
    ActiveSheet.Range("A1").End(xlDown).Offset(1).Insert Shift:=xlDown
    Application.CutCopyMode = False
End Sub

 

[BOBBEY]

It inserts the new block off cells 6 rows up from the last cell and puts the 6 rows under the new cell block. It also shifts all the rows under it down instead of just pasting it in that area. Thanks again for the help.

 

[MARK858]

Not at home yet so cant post any code at the moment . Can you just clarify do you want no blank rows inbetween and are there other blank cells in the column?
You can't just paste as it will overwrite the data below.

 

[BOBBEY]

I do not need any blank rows in between. The place where the new group of cells would be going shouldn't have anything to override. I will always be deleting 16 rows of data at a time and adding 16 rows of data. So if I have 16 rows with data then 16 blank rows then more data I want the "newjob" group of cells which is always 16 rows to go into the balnk 16 rows. If there are none in the middle then just go to the end. Hope this makes sense. No worries on when you can reply I am in no hurry. Thank You

 

[MARK858]

If I read it right then it is actually easier code.
Try

Sub newend()
    Dim New_Job As String
    Sheets("Master").Range("New_Job").EntireRow.Copy ActiveSheet.Range("A1").End(xlDown).Offset(1)
    Application.CutCopyMode = False
End Sub

 

[BOBBEY]

It cuts the bottom 6 rows off of the group above it. Then If I run it again to add another after that one where its blank it doesn't do anything.

 

[MARK858]

It cuts the bottom 6 rows off of the group above it

Sorry can't see how that can happen (and doesn't with my data). .End(xlDown) finds the last cell with data in a contiguous range and Offset(1) moves it down 1 row i.e. the first blank cell in Column A after A1.

If I run it again to add another after that one where its blank it doesn't do anything.

If you run it again it would place the 16 rows wherever the next blank cell in the column is and if there isn't a blank cell within the data then it would add it to the bottom of the data.
The only way it would appear to do nothing is if you were not on or made the made the correct sheet active.

I think you need to post a sample of your data as there is something that you haven't stated.

To post a screenshot see the links in my signature.

 

[MARK858]

There is one more question when you bring up New_Job in in the name box what range exactly is it highlighting?
Or check the Name Manager.

 

[BOBBEY]

This is getting really hard to explain with out a sample like you suggested. I figured out another easy way to do what I am trying to do. The first code you gave me to go to the bottom every time works for 99 percent of what I have to do. Thanks again for all the help.