Applying settings across Worksheets

[apache007]

Hi all,


I have worksheet A and I have implemented the following:

- Adding/remove coloumns/rows
- Formatting and Conditional formatting
- Formulas
- Data validations
- Security- User ranges + passwords, etc

I have worksheet B with a coloumn that defines the tab sheet with 20 rows in
them.
I would like to copy WORKSHEET A automatically and create 20 worksheets with
the name defines in WORKSHEET B.

How do I do that without INSERT them one by one.

Thanks in advance.

 

[JLatham]

Try this on a copy of your workbook to see if it works like you want it to.
Open the copy and press [Alt]+[F11] to open the VB Editor, then choose Insert
--> Module and copy and paste the code below into the module. You can then
actually click anywhere in the code and press [F5] to run it, or go back to
the workbook and use Tools --> Macro --> Macros to select and [Run] it.

Sub CopyWSA()
Dim anyWS As Worksheet
Dim LC As Integer

Set anyWS = ThisWorkbook.Worksheets("Worksheet A")
For LC = 1 To 20
anyWS.Copy after:= _
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets _
.Count)
Next
Set anyWS = Nothing
MsgBox "Done - without Renaming"
End Sub

 

[apache007]

Hi JLatham,

It works good.

However, it does not rename the TAB using the name on WORKSHEET B.

How do I do that?

Try this on a copy of your workbook to see if it works like you want it to.
Open the copy and press [Alt]+[F11] to open the VB Editor, then choose Insert
--> Module and copy and paste the code below into the module. You can then
actually click anywhere in the code and press [F5] to run it, or go back to
the workbook and use Tools --> Macro --> Macros to select and [Run] it.

Sub CopyWSA()
Dim anyWS As Worksheet
Dim LC As Integer

Set anyWS = ThisWorkbook.Worksheets("Worksheet A")
For LC = 1 To 20
anyWS.Copy after:= _
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets _
.Count)
Next
Set anyWS = Nothing
MsgBox "Done - without Renaming"
End Sub

Hi all,


I have worksheet A and I have implemented the following:

- Adding/remove coloumns/rows
- Formatting and Conditional formatting
- Formulas
- Data validations
- Security- User ranges + passwords, etc

I have worksheet B with a coloumn that defines the tab sheet with 20 rows in
them.
I would like to copy WORKSHEET A automatically and create 20 worksheets with
the name defines in WORKSHEET B.

How do I do that without INSERT them one by one.

Thanks in advance.