Macro to copy a worksheet doesn't copy buttons and controls

urz2006

New Member
Joined
Jan 9, 2019
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hello - apologies if this is a really basic thing but I know nothing about coding and am struggling to understand why what I thought was a simple macro is not performing as expected.

I have a whole series of worksheets, one for each hotel I am recording information about.

I have a master blank one called ".New Hotel Sheet" which I will use as a master template. (it it .New..... so it sorts to the beginning of the worksheet tabs)

I created a button, then linked and recorded a macro (called "NewHotel"), whereby I selected this "New Hotel Sheet", selected the entire worksheet, then added a new sheet and pasted the details across.

For some reason, this new sheet does not however copy the other buttons or form controls such as scroll boxes I have on the master document.

I have copied the code from the VBA window below if anyone can shed any light. I'm probably doing something really stupid, but I thought this would be a simple macro to record and that this was the point of a macro/button - to simplify repeated tasks? I just can't understand what I'm doing wrong. I f I manually copy and then paste the worksheet then it copies the buttons and scroll box controls fine, so what am I missing when I do the exact same steps but having pressed to record the macro on it yet then running that macro produces a different result?

Thanks so much to anyone who can help a VBA/macro newbie :)

Ian



Sub NewHotel()
'
' NewHotel Macro
'

'
Sheets(".New Hotel Sheet").Select
Cells.Select
Selection.Copy
Sheets("1 INDEX").Select
Sheets.Add After:=ActiveSheet
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Range("D13").Select
End Sub
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
You should use the Sheet.Copy method:
VBA Code:
Sub NewHotel()
    Sheets(".New Hotel Sheet").Copy after:=Sheets("1 INDEX")
End Sub

Bye
 
Upvote 0
Solution
You should use the Sheet.Copy method:
VBA Code:
Sub NewHotel()
    Sheets(".New Hotel Sheet").Copy after:=Sheets("1 INDEX")
End Sub

Bye
Thank You Anthony47 ! Sorry if this was so simplistic, I thought the idea of a macro was for it to replicate what I was doing with the need to write the code, but whatever paste method I tried (standard, special etc) it just refused to bring these buttons and controls with it. Thanks so much for taking the time to help me :)
 
Upvote 0
Are you sorry I hadn't to work hard? :biggrin:
Quite often there is a simpler way!
 
Upvote 0

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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
Back
Top