Using a Macro on every new Worksheet

[m166612]

I have a problem with a macro that I've designed.

I need a macro that will copy and paste a data table from a TEMPLATE tab into every new tab that I create. I need this macro to then protect the same cells within each new tab.

Currently, my macro works for the first new tab I insert- but doesn't work when I insert a second, third tab etc.

Any suggestions would be welcomed on how to get the macro to work on each new tab please.

Many Thanks,

Nathan

 

[Trevor G]

Welcome to the Forum Nathan.

Can you show your code you are currently using.

 

[p45cal]

Some existing code might be useful.

 

[m166612]

Thankyou for offering your help so quickly.

Below is the code that has been used. (I hope it makes sense to you as I don't know what it shows).

What the code should show is that I copy and paste data from TEMPLATE tab to a new worksheet. Then, I highlight all the cells in that new worksheet that I want to be unlocked and carry on to protect and lock the remaining cells within that worksheet.

My problem occurs when I then open up another new tab and try the same process.

Code:

Sub New_Site_Datatable()
'
' New_Site_Datatable Macro
' Macro recorded 21/10/2011 by m166612
'
' Keyboard Shortcut: Ctrl+n
'
Cells.Select
Selection.Copy
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Sheet21").Select
Cells.Select
ActiveSheet.Paste
Range("B6:C10,L5:M9,D16:F49,N16:P32,S16:U32,X16:Z32").Select
Range("X32").Activate
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 33
Range("B6:C10,L5:M9,D16:F49,N16:P32,S16:U32,X16:Z32,N40:P56,S40:U56,X40:Z56"). _
Select
Range("X40").Activate
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range( _
"B6:C10,L5:M9,D16:F49,N16:P32,S16:U32,X16:Z32,N40:P56,S40:U56,X40:Z56,D57:F90,D99:F132" _
).Select
Range("D99").Activate
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 104
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 111
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 113
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 115
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 121
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 131
ActiveWindow.ScrollRow = 132
Range( _
"B6:C10,L5:M9,D16:F49,N16:P32,S16:U32,X16:Z32,N40:P56,S40:U56,X40:Z56,D57:F90,D99:F132,D141:F174" _
).Select
Range("D141").Activate
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 148
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 154
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 156
ActiveWindow.ScrollRow = 157
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 161
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 166
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 169
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 174
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 176
Range( _
"B6:C10,L5:M9,D16:F49,N16:P32,S16:U32,X16:Z32,N40:P56,S40:U56,X40:Z56,D57:F90,D99:F132,D141:F174,D183:F216" _
).Select
Range("D183").Activate
ActiveWindow.ScrollRow = 190
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 193
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 199
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 201
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 206
ActiveWindow.ScrollRow = 207
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 209
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 211
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 218
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 220
Range( _
"B6:C10,L5:M9,D16:F49,N16:P32,S16:U32,X16:Z32,N40:P56,S40:U56,X40:Z56,D57:F90,D99:F132,D141:F174,D183:F216,D225:F258" _
).Select
Range("D225").Activate
ActiveWindow.ScrollRow = 230
ActiveWindow.ScrollRow = 231
ActiveWindow.ScrollRow = 232
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 236
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 245
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 247
ActiveWindow.ScrollRow = 248
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 251
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 255
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 257
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 260
Range( _
"B6:C10,L5:M9,D16:F49,N16:P32,S16:U32,X16:Z32,N40:P56,S40:U56,X40:Z56,D57:F90,D99:F132,D141:F174,D183:F216,D225:F258,D267:F300" _
).Select
Range("D267").Activate
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 274
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 277
ActiveWindow.ScrollRow = 278
ActiveWindow.ScrollRow = 279
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 282
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 285
ActiveWindow.ScrollRow = 286
ActiveWindow.ScrollRow = 287
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 289
ActiveWindow.ScrollRow = 290
ActiveWindow.ScrollRow = 291
ActiveWindow.ScrollRow = 292
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 294
ActiveWindow.ScrollRow = 295
ActiveWindow.ScrollRow = 296
ActiveWindow.ScrollRow = 297
ActiveWindow.ScrollRow = 298
ActiveWindow.ScrollRow = 299
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 304
ActiveWindow.ScrollRow = 306
ActiveWindow.ScrollRow = 307
ActiveWindow.ScrollRow = 308
ActiveWindow.ScrollRow = 309
ActiveWindow.ScrollRow = 310
ActiveWindow.ScrollRow = 311
ActiveWindow.ScrollRow = 312
ActiveWindow.ScrollRow = 313
ActiveWindow.ScrollRow = 314
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 316
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 316
ActiveWindow.ScrollRow = 315
ActiveWindow.ScrollRow = 314
ActiveWindow.ScrollRow = 312
ActiveWindow.ScrollRow = 310
ActiveWindow.ScrollRow = 308
ActiveWindow.ScrollRow = 306
ActiveWindow.ScrollRow = 303
ActiveWindow.ScrollRow = 300
ActiveWindow.ScrollRow = 297
ActiveWindow.ScrollRow = 294
ActiveWindow.ScrollRow = 291
ActiveWindow.ScrollRow = 289
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 276
ActiveWindow.ScrollRow = 273
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 266
ActiveWindow.ScrollRow = 264
ActiveWindow.ScrollRow = 261
ActiveWindow.ScrollRow = 259
ActiveWindow.ScrollRow = 256
ActiveWindow.ScrollRow = 254
ActiveWindow.ScrollRow = 252
ActiveWindow.ScrollRow = 249
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 242
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 237
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 233
ActiveWindow.ScrollRow = 232
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 227
ActiveWindow.ScrollRow = 225
ActiveWindow.ScrollRow = 224
ActiveWindow.ScrollRow = 221
ActiveWindow.ScrollRow = 219
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 215
ActiveWindow.ScrollRow = 212
ActiveWindow.ScrollRow = 210
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 204
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 198
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 194
ActiveWindow.ScrollRow = 191
ActiveWindow.ScrollRow = 188
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 184
ActiveWindow.ScrollRow = 180
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 173
ActiveWindow.ScrollRow = 170
ActiveWindow.ScrollRow = 167
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 160
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 157
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 151
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 146
ActiveWindow.ScrollRow = 144
ActiveWindow.ScrollRow = 143
ActiveWindow.ScrollRow = 142
ActiveWindow.ScrollRow = 141
ActiveWindow.ScrollRow = 139
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 136
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 130
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 127
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 112
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 101
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 97
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 86
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 83
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 60
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 48
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 43
ActiveWindow.ScrollRow = 42
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 39
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 34
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 246
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 235
ActiveWindow.ScrollRow = 229
ActiveWindow.ScrollRow = 222
ActiveWindow.ScrollRow = 216
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 202
ActiveWindow.ScrollRow = 192
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 176
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 163
ActiveWindow.ScrollRow = 155
ActiveWindow.ScrollRow = 147
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 103
ActiveWindow.ScrollRow = 98
ActiveWindow.ScrollRow = 91
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 80
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 52
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 33
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End Sub

 

[m166612]

In the code, the Active Sheet.paste is highlighted in yellow which suggests to me that's where the problem may lie... but I don't really know at all :S

 

[Trevor G]

If its straight forward copy cells from Template and paste into new sheet try this

You can remove all of your scrolling which was part of your recorded macro

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> copyCells()<br>Sheets("Template").Activate<br>Cells.Copy<br>Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = Format(Date, "DD MMM YYYY")<br>Cells.PasteSpecial xlPasteAll<br>Range("A1").Select<br>ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True<br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[p45cal]

In the code, the Active Sheet.paste is highlighted in yellow which suggests to me that's where the problem may lie... but I don't really know at all :S

There's usually an error message too - what does that say?

I suspect the sheet you're copying to is already protected.

 

[m166612]

Trevor- thankyou for your code, I tried it but it opened a whole new workbook so not sure if I may have done something wrong there :S Daft question but should I have put all the cells that I wanted highlighted in Sub copyCells()??


p45cal- you are right, the sheet it is pasting to is protected. This is because when I open up a second tab and try the macro, it tries to paste it to the first tab I opened which, by that point, it is protected due to the macro working first time round Bad times.

I was hoping there may be a way, whilst excel records my macro, that I could click a button to make it realise it had to work in the next new tab

 

[p45cal]

I suspect this will do it for you:

Sub copyCells()
Sheets("Template").Copy After:=Sheets(Sheets.Count)
Set Newsht = ActiveSheet
Newsht.Name = Format(Now, "DD MMM YYYY hh_mm")
End Sub

The new sheet is a copy of the Template sheet with a different name. It inherits whatever was protected on the Template sheet, as well as the protected status of the sheet.