Mulitple tabbing

QuestionEdit

QUESTION: I have put a lot of work into anworksheet that has option buttons, command buttons, cell formating, embedded objects, many formulas and conditional formatting etc. the spreadsheet is for daily operation of a treatment plant that now needs to be replicated 30 more times (each tab representing the day of the month). the command buttons are for relocating to different areas of the current tab and printing different areas of the current tab.

Copy and poaste does not seem to work completely and I dont want to spend months formatting each tab. Is there an easy way to replicate each tab and also have the command and option buttons etc work for each sheet.

ANSWER: I need to know what doesn't work when you do copy and paste -- are you copying the cells then the objects? Or are you copying the sheet tab (which is the better way to go).?

---------- FOLLOW-UP ----------

QUESTION: Sorry for the late reply,

When I copy the tab, the command buttons that are specific to each tab (or supposed to be) do not copy correctly.

The original tab has several command buttons that will either relocate the user to areas of the tab, or print different areas. There are also option buttons that allow the user to select items, conditions etc.

I want to be able to replicate all these buttons for each tab (which will represent each day of the month). Right now I have one tab created, but need 30 more exactly the same. The user can then just click on a tab for each day of the month.

What I am having an issue with, is the tab is copied, but nothing is copied in visual basic. The actual command buttons etc will copy but not the coding, so when clicked, automatically go to the original tab. I just thought there might be an easier way to copy the coding to these as well, as opposed to recreating every single one for each tab.

ANSWER: In trying to replicate your problem I found no issue. Yes, the macro runs the same code, so it depends on what you have in your code. For me to help you you're going to either have to post the exact code attached to one of the buttons which no longer works, or send me a workbook which demonstrates the issue -- it should work. If you're sending me a wb, send to bobumlas@yahoo.com, use subject of "AllExpertsQ" and repeat what needs doing.

---------- FOLLOW-UP ----------

QUESTION: Here are some examples of the current coding:

Private Sub CommandButton1_Click() ThisWorkbook.Sheets("1").Activate ThisWorkbook.Sheets("1").Range("A118").Select End Sub

Private Sub CommandButton10_Click() ThisWorkbook.Sheets("1").Activate ThisWorkbook.Sheets("1").Range("c170:q213").Select ThisWorkbook.Sheets("1").PageSetup.PrintArea = "c170:q213" ThisWorkbook.Sheets("1").PrintPreview End Sub

Private Sub CommandButton11_Click() ThisWorkbook.Sheets("1").Activate ThisWorkbook.Sheets("1").Range("A177").Select End Sub

Private Sub CommandButton12_Click() ThisWorkbook.Sheets("1").Activate ThisWorkbook.Sheets("1").Range("A118").Select End Sub

Private Sub CommandButton13_Click() ThisWorkbook.Sheets("1").Activate ThisWorkbook.Sheets("1").Range("BB235").Select End Sub

They are rather simple and most are redirecting to various locations on Sheet "1" but when I go to copy sheet "1" to make 30 more, the coding does not copy. the actual command buttons do, but the command buttons retain the original coding for sheet "1".

I want the coding to change also.. for instance.. I make Sheet "2" and the code for the command buttons that also copied to sheet "2" will now look like this:

Private Sub CommandButton1_Click() ThisWorkbook.Sheets("2").Activate ThisWorkbook.Sheets("2").Range("A118").Select End Sub

Private Sub CommandButton10_Click() ThisWorkbook.Sheets("2").Activate ThisWorkbook.Sheets("2").Range("c170:q213").Select ThisWorkbook.Sheets("2").PageSetup.PrintArea = "c170:q213" ThisWorkbook.Sheets("2").PrintPreview End Sub

Private Sub CommandButton11_Click() ThisWorkbook.Sheets("2").Activate ThisWorkbook.Sheets("2").Range("A177").Select End Sub

Private Sub CommandButton12_Click() ThisWorkbook.Sheets("2").Activate ThisWorkbook.Sheets("2").Range("A118").Select End Sub

Private Sub CommandButton13_Click() ThisWorkbook.Sheets("2").Activate ThisWorkbook.Sheets("2").Range("BB235").Select End Sub

I want to be able to do this without manually adding them for each sheet. I am sorry if I am not explaining it well, I am still learning all this.

AnswerEdit

Leave off ALL the Activate statements - unnecessary because the sheet is already active. Don't bother with ThisWorkbook.Sheets("whatever").range(...).Select, just use Range(whatever).select

Now it'll work on all sheets. For example (notice what I did with ActiveSheet instead of the specific one for printing): Private Sub CommandButton1_Click() Range("A118").Select End Sub

Private Sub CommandButton10_Click() ActiveSheet.PageSetup.PrintArea = "c170:q213" ActiveSheet.PrintPreview End Sub

Private Sub CommandButton11_Click() Range("A177").Select End Sub

Private Sub CommandButton12_Click() Range("A118").Select End Sub

Private Sub CommandButton13_Click() Range("BB235").Select End Sub