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

urz2006

New Member
Joined
Jan 9, 2019
Messages
3
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,969
You should use the Sheet.Copy method:
VBA Code:
Sub NewHotel()
    Sheets(".New Hotel Sheet").Copy after:=Sheets("1 INDEX")
End Sub

Bye
 
Solution

urz2006

New Member
Joined
Jan 9, 2019
Messages
3
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 :)
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
1,969
Are you sorry I hadn't to work hard? :biggrin:
Quite often there is a simpler way!
 

Watch MrExcel Video

Forum statistics

Threads
1,119,255
Messages
5,576,990
Members
412,759
Latest member
Jackuk127
Top