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

BOBBEY

New Member
Joined
May 6, 2013
Messages
26
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"
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,825
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What error message are you getting because the only error I can think of on that line is if the named range is spelt differently to the name in the macro or there is a leading space etc.?

The spelling has to be exact.
 

BOBBEY

New Member
Joined
May 6, 2013
Messages
26
Thanks for the response. I get Run-time error '1004': Selact method of Range class failed. I checked all spelling.
 

BOBBEY

New Member
Joined
May 6, 2013
Messages
26
Does the code have to go somewhere specific Ie Under the sheet, workbook, or module? Still kinda new to this.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,825
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Try either of the codes below
Code:
Sub newend()
'
' newend Macro
Dim New_Job As String
With Sheets("Master")
.Range("New_Job").Copy
.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
End With
Application.CutCopyMode = False
End Sub
Code:
Sub newend2()
'
' newend Macro2
Dim New_Job As String
Sheets("Master").Range("New_Job").Copy Destination:=Range("A65536").End(xlUp).Offset(1, 0)
End Sub

Does the code have to go somewhere specific Ie Under the sheet, workbook, or module? Still kinda new to this.

Put it in a standard module
 

BOBBEY

New Member
Joined
May 6, 2013
Messages
26
The first code puts the new group of cells on the "Master" sheet. Is there a way to always put it on the active sheet when the macro is run. Thank you
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,825
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

It wasn't designed to move it to the other sheet. It was done to make sure there were no other issues. Are you sure you want it to go to the ActiveSheet and not a particular sheet as normally in principle it is not a good idea.
Answer that question and I will amend it when I get in tonight as I hate doing even simple coding on my phone
 

BOBBEY

New Member
Joined
May 6, 2013
Messages
26
The group of cells on "Master" is a blank block of codes for a new job. On the next sheets I have different customers. So when I go to each customers sheet I need it to insert the block from "Master" to the bottom of just that sheet. Just trying to clarify what I am looking for. Again thanks so much for the help.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,825
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If all you have on the Master sheet is literally just the codes then I don't think there is much we can do to make the code more resilient.

Just be careful and make sure you are on the Sheet you want the codes to go to, because remember you won't be able to undo the action afterwards.

Test the code on a copy of your workbook for the same reason as given above.

Code:
Sub newend()
' newend Macro2
Dim New_Job As String
Sheets("Master").Range("New_Job").Copy Destination:=ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,181
Messages
5,594,720
Members
413,926
Latest member
andycap

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top