Copy Sheet VBA

[kieranm105]

Hi
I have a Purchase Order sheet that I want to let the user copy a new order based on the template sheet (template will be hidden).
I want the first new order sheet be named "1" and then the next new ones "2,3,4 etc" (all placed before "Shipping List").

This is the code I have so far that works to a point:

Please Login or Register  to view this content.

I would like it to do the following:
- Copy a new order (before Shipping List) and name it 1
- Copy further new orders and name them sequentially
- On the "Summary Sheet" automatically fill in the formulae (shown as REF! on the example attached) going down the table
- Automatically say yes to all the Table rename error message boxes

[dflak]

I think this should do what you want.

[dflak]

Ooops forgot the formulas on the summary sheet. I'll look at that in the next break.

[dflak]

What is that formula supposed to be?

[kieranm105]

Thanks dflak, that works but copies the summary sheet not the template order sheet.

The formulas on the summary sheet that I want to update automatically show the following:
> Column A (Order number) - the Purchase Order number at the top of each order sheet (cell - H3 on each order sheet)
> Column C (Company) - Shows the company that the order is for (cell - F8 on each order sheet)
> Column G (Actual costs) - Shows the actual cost for each order (cell - I36 on each order sheet)

I also need it to automatically accept the Table name message box warnings.
The template sheet needs to be hidden as well (I've left it visible for now).
Kieran

[dflak]

I also noticed that you would also like a hyperlink to the page from the Summary Sheet.

[kieranm105]

Thanks dflak, that works great.
Kieran

[kieranm105]

I have a quick question on this.
It works great on everyone's PC apart from one! It pops up a runtime error and highlights this code.

Please Login or Register  to view this content.

When I looked at the hidden sheets, it had copied the Template sheet a few times and named them Template (2), Template (3) etc. But works perfectly on other PCs????

[dflak]

Hmmm. It shouldn't really matter if there are Template (3), Template (4) etc. As long as a Template (2) exists this line of code should take you to it.

The code should not be creating the other copies, but they should not cause an error.

Can you duplicate the error with a "fresh" copy of the spreadsheet?

[kieranm105]

Hi dflak
No it only happens on that one PC. Instead of copying the template and naming them correctly it just added more templates and then threw the error up.
I tried it on mine and some other users PC's and it works fine. The only thing is that the PC in question had a recent upgrade to Win10 then had to have a factory reset back to Win8.1 as 10 wouldn't work.
There could be some update missing?

[dflak]

I'm going to take a guess - take a look at the spelling. The code is looking for Template - space - (2). Could it be that the malfunctioning machine is looking for a spelling without the space.

I'll relook at the code - I don't like the idea of depending on a name to make things work. This might be one of those rare instances where ActiveSheet is appropriate.

[dflak]

That took less time than I thought. Comment out the offending line and change the code to reflect the line highlighted in red below

Please Login or Register  to view this content.

[kieranm105]

This gets even more confusing now!
I've redone the code as above, which works on all the PC's as it should. But this one PC now copies the template and it is named 'Template (2)' and then renames the 'Shipping List' sheet to 1??
I think his PC has some bugs in it somewhere!!
Kieran

[dflak]

Are there differences in the versions of Excel?

I am reluctant to depend on a sheet name unless I am sure what that sheet name is. I am also leery about using activesheet since Excel and I may have a difference of opinion on what the active sheet is. Apparently that is what is happening here. The program on the other machines thinks that the recently copied sheet is Template (2). On the exception machine, it thinks that Shipping List is the active sheet.

What I can do is add the sheet after the last sheet so that I know that the last sheet in the book is the one I want to work with. Then after renaming it, move it to its proper position. This logic eliminates dependence on a name or active sheet. I'll give that a shot and get back to you.

[dflak]

Here is a new version with modified code

Please Login or Register  to view this content.

[kieranm105]

That worked, but hid the new sheet 1.
We're checking for updates and reinstalling office 365 on that PC.
Kieran

[dflak]

Nothing in the code says hide the sheet . Yeah, I think it's worth looking at office on that machine.

[kieranm105]

This has been working great for a few months, now it is hiding the 5th sheet onwards on several pc's??
The full code that I have is below.
Any ideas?

Please Login or Register  to view this content.

[kieranm105]

This is now hiding the new sheet on everyone's pc. I've had a look and made it unhide the template sheet before copying it and then hiding it straight after.

Please Login or Register  to view this content.