Applying settings across Worksheets

  • Thread starter Thread starter apache007
  • Start date Start date
A

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.
 
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 JLatham,

It works good.

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

How do I do that?



JLatham said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top