copy to new workbook, delete sheet & repeat NOT SOLVED

[Eric Milne]

Hi all,

I've written code to fill in a hidden template sheet, print it out then reset the sheet to template a number of times using a loop.

What i'd like to do is as well as print the sheet out, save it to a new workbook and rename the sheet before it is reset then repeat. This would give me all the printouts in one seperate workbook to edit if need be.

Ideally i'd like to have the new workbook created in the same filepath as the workbook im using.

Can anyone give me some clues?

Thanks in advance,

Eric

 

[MarkAndrews]

Please can you post your code

Thanks

 

[Eric Milne]

Ok, Its a very large procedure with 6 nested loops so i've taken a snippet of code that i've come up with so far that kindof does half of what i want...

the line "Sheets("CTR").Copy" saves the sheet to a new workbook, but i want to give the sheet a unique name which i have in a string variable called "Discipline" then save it as "CTRs" with the same filepath as the workbook im working with...

Dim b As String
b = Application.ActiveWorkbook.Name

            'send the CTR sheet to the printer
            Sheets("CTR").PrintOut copies:=1, collate:=True
            'save CTR sheet in new workbook
            Sheets("CTR").Copy
            GoTo 3
            End If
        
        
3: 'reset the populated CTR and replace with template CTR sheet before next discipline values are populated

    Workbooks(b).Worksheets("CTR").Delete
    Workbooks(b).Worksheets("CTR Blank").Copy After:=Workbooks(b).Worksheets("CTR Blank")
    Workbooks(b).Worksheets("CTR Blank").Name = "CTR"
    Workbooks(b).Worksheets("CTR Blank (2)").Name = "CTR Blank"

        GoTo 1

The above code is within a loop, I want to save the new workbook i've created next to the workbook im working with then find it again when the next sheet is to be saved into it.

Cheers,

Eric

 

[Eric Milne]

Bump