Copy & Paste Macro (Buttons) into new worksheet

[poexaa2]

Can anyone help please.

Having read some of the VBA queries on here I'm embarrassed to ask but am still new to this VBA thing so...

I have recorded some very simple 1 line Macros with buttons assigned to automate navigation around a worksheet to different ranges. Typical code=

Range("DashboardSum").Select

and that's it!!

This does the job in the worksheet I am in. I then want to copy the entire contents of the worksheet and paste into another for the next months data. All fine, however when I hit a macro button in the new worksheet I get the 'Run-time error '1004': Select method of Range class failed' message. I am not pasting into a new file, just a new sheet that I have inserted.

I have looked around different forums for related threads but they all appear to be way too complicated for what must surely require a simple bit of editing.

Can someone with a bigger brain than I please help.

thanks

poexaa2

 

[VoG]

Something like this should work

Range("DashboardSum").Copy Destination:=Sheets("Sheet2").Range("A1")

 

[poexaa2]

Thx very much VoG

Bear with me! So I edit my existing code to match your suggestion. But then it does not work in the original spreadsheet, even before I have attempted to copy and paste anything. Why is the element of the code .Range ("A1") necessary? What am I missing here???

Really sorry, I obviously need spoon-feeding.

P

 

[VoG]

Can you post the code that you have and explain in words what you are trying to do.

 

[poexaa2]

Can you post the code that you have and explain in words what you are trying to do.

Thx

This is the full code:-

Sub GoToDashboard()
'
' GoToDashboard Macro
' Macro recorded 05/09/2011 by Cliff
'
' Keyboard Shortcut: Ctrl+d
'

Range("DashboardSum").Select
End Sub

And that's it. All I am trying to do is move around a spreadsheet where the client wants various linked schedules (that I have Rangenamed) all on one worksheet for each period. The macro's are all the same (EXCEPT FOR THE RANGE NAME) as above. The associated buttons been copied and pasted above each schedule on the worksheet so that you can move from one schedule to another without having to go hunting for their location. At the start of a new period a new worksheet is inserted into the file and we then want to copy and paste the entire worksheet from the previous period onto the new one. The macro buttons do not work in the new spreadsheet that has been pasted and I get the error message described above.

Hope this helps.

Thx again.

P

 

[VoG]

That will (as you've discovered) generate an error if the worksheet with that range is not active. Try

Sub GoToDashboard()
'
' GoToDashboard Macro
' Macro recorded 05/09/2011 by Cliff
'
' Keyboard Shortcut: Ctrl+d
'

Application.Goto Range("DashboardSum"), True
End Sub

 

[poexaa2]

That will (as you've discovered) generate an error if the worksheet with that range is not active. Try

Sub GoToDashboard()
'
' GoToDashboard Macro
' Macro recorded 05/09/2011 by Cliff
'
' Keyboard Shortcut: Ctrl+d
'
 
Application.Goto Range("DashboardSum"), True
End Sub

Fantastic!!, thank you. Works wonderfully.

Just joined Mr Excel, hopefully I will be able to help someone else at some point. Thanks again, much appreciated.

 

[poexaa2]

That will (as you've discovered) generate an error if the worksheet with that range is not active. Try

Sub GoToDashboard()
'
' GoToDashboard Macro
' Macro recorded 05/09/2011 by Cliff
'
' Keyboard Shortcut: Ctrl+d
'
 
Application.Goto Range("DashboardSum"), True
End Sub

Thanks v.much!! Fantastic, works wonderfully. Hopefully, I will be able to assist someone in the future with an Excel query. Much appreciated.

 

[poexaa2]

Thanks v.much. Fantastic, works wonderfully. Hopefully I will be able to assist someone with a query in the future. Much appreciated.

 

[poexaa2]

Thanks for this, fantastic works wonderfully. Much appreciated.