Applying settings across Worksheets

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.
 
J

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
 
A

apache007

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top