Trying to add variables into Worksheet statements and can't get the right syntax

[Freshpetguy]

hello to one and all, and thank you for a little help. I'm a novice and have learned code on my own (ugh) but I've used Excel recording macros as a start, and read what I needed from articles across the net. I'll post the excel recording of what I'm doing, and the variables. I'm copying a sheet from a master file, putting it into a new file, and then creating additional copies of the same tab in the new file. This approximates our electronic batch making file process in the plant. I'm just trying to automate it. Example: Batches 1 - 4 from formula Dog123. Copy tab Dog123 in the master file, open new file, paste copy of Dog123 in the new file, give the tab the header Dog123 (1), the copy Dog123 (1) three more times changing the header for the tab each time to Dog123 (2), Dog123(3)... Sorry about being long winded here, but its hard to describe.
Thanks for the input:
Using Formula from a spreadsheet and pulling it into the name of the sheet
Using count to add the extra labeling to the tab name and also count how many times to repeat the process before going to the next batch

AddMoreSheets:

Sheets(Formula).Select
    Sheets(Formula & Count).Copy Before:=Sheets(Count)
    Sheets("Formula & (Count + 1)").Select
    Range("H2").Select
    ActiveCell.FormulaR1C1 = "Count +1"
If Count > NoBatches Then GoTo Meals
Count = Count + 1
GoTo AddMoreSheets

This section came from the recording

'Sheets("FR 2").Select
    'Sheets("FR 2").Copy Before:=Sheets(2)
    'Sheets("FR 2 (2)").Select
    'Range("H2").Select
    'ActiveCell.FormulaR1C1 = "2"
    'Range("H3").Select
    'Sheets("FR 2 (2)").Select
    'Sheets("FR 2 (2)").Copy Before:=Sheets(3)
    'Sheets("FR 2 (3)").Select
    'Range("H2").Select
    'ActiveCell.FormulaR1C1 = "3"

[SamT]

Please correct or expand

You said (with my Q's and Comments):

hello to one and all, and thank you for a little help. I'm a novice and have learned code on my own (ugh) but I've used Excel recording macros as a start, and read what I needed from articles across the net. I'll post the excel recording of what I'm doing, and the variables.

I'm copying [SheetName (Dog123?)] from a master file, putting it into a new file, and then creating additional copies of the same [Sheet] in the new file. This approximates our electronic batch making file process in the plant. I'm just trying to automate it.

Example: Batches 1 - 4 from [What??? A formula??? ]. Copy [Sheet] Dog123 in the master file, open new file, paste copy of Dog123 in the new file, give the tab the [Name] Dog123 (1), the copy Dog123 (1) three more times changing the header for the tab each time to Dog123 (2), Dog123(3)... Sorry about being long winded here, but its hard to describe.

Thanks for the input:

Using Formula from a spreadsheet and pulling it into the name of the sheet [What does this mean( pulling a formula into a Name)?]

Using count to add the extra labeling to the tab name and also count how many times to repeat the process before going to the next batch.

------------------------------------------------------------

I am moving this to the Excel Forum for better response. SamT

[Freshpetguy]

okay. Let me give this a try again. I'd love to use the names of the files to help, but can't for proprietary reasons. With that said let me try again.
Part 1
The master file is located on our server. For this instance it is called "Formula Master".
It has on it a tab with one of our formulas with a given name of the formula called Dog123.
Dog123 is the first sheet in the file.
Part 2
To create a daily file
I give the new file a name (generally the date) for example "04.08.14"
I have the new file open
Go to the Master file, click on the Dog123 tab and create a copy
Paste the copy into "04.08.14"
Now my first tab is Dog123....
I then copy the Dog123 tab in file 04.08.14 and when excel creates the second file it gives it the label "Dog123 (2)
this process is repeated for the number of batches, in this instance let's say its two,
I then go back to the master to get a new recipe to copy in as batch 3 and what I'm trying to do is to be able to label the tab sheet
with the correct number of the batch to run for the day.

[Freshpetguy]

so I copy a new tab from the master called Dog345.
Copy it into the new file as the sheet before Sheet 1
When it copies into the file I have to go and rename the tab to Dog345 (3) so we know it is the third batch of the day to run.

[SamT]

OK, Every time there is a batch ran, you get a file "Named" Formula Master. All day long you need to copy the sheet Dog123 into the Daily file but with an incremented name ie: dog123(1, 2 ,3,...)

Since the new Daily file always starts with a Sheet1, the newly pasted-in sheet name is always changed to

WorkBooks(DailyFile).Sheets("Dog123").Name = "Dog123(" & WorkBooks(DailyFile).Sheets.Count -1 & ")"

The "- 1" handles the presence of Sheet1. If you rename the Dog123 sheet before pasting it , then omit the "-1".

[Freshpetguy]

OK, Every time there is a batch ran, you get a file "Named" Formula Master. All day long you need to copy the sheet Dog123 into the Daily file but with an incremented name ie: dog123(1, 2 ,3,...)

Since the new Daily file always starts with a Sheet1, the newly pasted-in sheet name is always changed to

WorkBooks(DailyFile).Sheets("Dog123").Name = "Dog123(" & WorkBooks(DailyFile).Sheets.Count -1 & ")"

The "- 1" handles the presence of Sheet1. If you rename the Dog123 sheet before pasting it , then omit the "-1".

[Freshpetguy]

I plugged it in this morning and gave it a whirl and it worked. Thank you very much. I appreciate your time and efforts.
Thanks,
Steve