Help with Macro to create workbook and copy data

[JV0710]

Hi
Please can I get some help with this query:

In my excel workbook I have a "Summary" sheet with data in range: A1:Z50
in Cell Z1 I have a "Name1"
in Cell Z3 I have "Name2"

I would like a Macro that will:

Find an Excel file in directory C:\
Name of file must be the name in Cell Z1 "Name1"

Create an additional sheet in that file
The Sheet name must be the name in Cell Z3 "Name2"

Then select data in Range A1:Z50 from my "summary sheet" and
Copy and Paste Data and Format to the sheet created in the file.

****************************************************

If the file with the name in cell Z1 "Name1", cannot be found, then create the file in directory C:\

Create an additional sheet in that file
The Sheet name must be the name in Cell Z3 "Name2"

Then select data in Range A1:Z50 from my "summary sheet" and
Copy and Paste Data and Format to the sheet created in the file.



Thank you

 

[JV0710]

Hi

Yes, when I close the file after the first is created I save it,

When I save the sheet after a second time running the macro it saves without saying that "the sheet exists - do you want to replace" - it just saves.

and There is only one file named: "Joe%20-%20Wedge%20Tracking%20Workbook.xlsx" in "C:\".


Thanks

 

[Yard]

So you're saying:

1. you run the code once and it adds a sheet "12345" to the C: drive workbook
2. you save the C: drive workbook and close it
3. you run the code a second time and it adds a a sheet "12346" to the C: drive workbook, but the sheet "12345" is no longer there

I'm sorry but I just don't see how the code I gave you can produce that result.

Do you know how to step through code line by line? We need to figure out at which point the code is diverging from the result you want to achieve.

 

[JV0710]

Hi Yard

Sorry that I am replying so late . . . I had to attend some meetings

Yes . . . that is Exactly what I am saying:

1. I run the code once and it adds a sheet "12345" to the C: drive workbook
2. I save the C: drive workbook and close it
3. I run the code a second time and it adds a a sheet "12346" to the C: drive workbook, but the sheet "12345" is no longer there

I do not know how to step through code line by line - - - I am sorry

 

[Yard]

And you copied the code exactly as I posted it?

I'm running it now, time after time, and it creates sheet 12345, then 12346, then 12347 etc etc etc.

To step through code, put your cursor within the code in the Visual Basic Editor and press F8. Every time you press F8 it will execute the line highlighted in yellow. Perhaps that will help you work out where it is doing something you don't expect.

Other than that I must conclude that we have wires crossed in a way I can not comprehend, sorry!

 

[JV0710]

Hi Yard

yes I copied the code exactly as you posted.

Thank you very much for your patience and help

I will go through the code line by line and try to figure it out and let you know

Thank you once again for your help

Joe