Name new sheet based on cell from another workbook

[jimmisavage]

I have a workbook where On Save it opens up a data sheet and creates a new sheet (if it doesn't already exist) based on a cell value. Or, at least that's what I want it to do.

I have this code, which I was hoping would take the cell value from Sheet2 cell A1 and name the new sheet that. However I don't really know how to make that work - currently it's set up to call the new sheet "Test" but I can't figure out how to change that. Can anyone offer some advice?

Please Login or Register  to view this content.

[jimmisavage]

I was hoping something like this could work:

Please Login or Register  to view this content.

[jimmisavage]

I think it's probably best to scrap the code above and try and explain what I need.

I have a workbook called IndividualData (the name itself will change, StuartData, SarahData, SamData etc) and a second workbook called AllData.
When I enter data into IndividualData and hit save I need this to happen:
1. Open AllData workbook
2. Check in AllData to see if a sheet exists with the name in IndividualData, sheet2, cell A1
a. If the sheet exists in AllData it will open that sheet, delete all data in that sheet and then copy & paste cells A:L from IndividualData Sheet1.
b. If the sheet doesn't exist in AllData it will create a new sheet and name it ( from IndividualData, sheet2, cell A1) and also copy and paste A:L from IndividualData Sheet1.
3. Save and close AllData
4. Save IndividualData and display a message to confirm update.

Can anyone help with this?

[Alf]

Perhaps something like this. But be aware that this code is untested!

Please Login or Register  to view this content.

Right-click on a sheet tab in the InduvidualData or whatever the file is named. Click on "View Code" and then click on "ThisWorkbook" in the "Project - VBA project" and copy the code to the new Window that opens.

You need to specify the right path for the AllData file, you may also have to change the xlsx ending to xlsm if "AllData" contains macro. As I don't know the size of the A:L range I guesstimated some values.

Alf

[jimmisavage]

Perhaps something like this. But be aware that this code is untested!

Please Login or Register  to view this content.

Right-click on a sheet tab in the InduvidualData or whatever the file is named. Click on "View Code" and then click on "ThisWorkbook" in the "Project - VBA project" and copy the code to the new Window that opens.

You need to specify the right path for the AllData file, you may also have to change the xlsx ending to xlsm if "AllData" contains macro. As I don't know the size of the A:L range I guesstimated some values.

Alf

Hi Alf,
Thanks for taking a crack at this; it almost worked but there are a couple of issues.
1. It seems to create a new sheet on the IndividualData workbook
2. It doesn't seem to create a new sheet in the AllData workbook and also doesn't appear to rename it either (it copies the data into Sheet1).

[Alf]

Please Login or Register  to view this content.

That is strange as the line

Please Login or Register  to view this content.

should ensure that the AllData file is the active one. Have you changed the path "C:\User\Myfolder\" to the proper one where the file AllData is found. I also assumed that AllData was an xlsx file but is it so? You have to change the file ending if this file is an xlsm file.

What you describe tells me that the "AllData" file is not opened this is why the macro work on the only opened file it can find i.e. the "Individual" file.

I would recommend you record a macro when you open the "AllData" file that will give you the proper path and right name of this file.

Alf

[jimmisavage]

Hi Alf,
It definately opens the AllData file and it even copies and pastes the data into it. It's just the new sheet and renaming the sheet that doesn't work for whatever reason

[jimmisavage]

If it helps I've attached the documents so you can see what I see
Data Sheet is the AllData sheet (I changed the name)
and Jimmi Savage is the IndividualData sheet

[Alf]

Hmm, interesting.

Could you please post the macro you recorded when you opened and closed the AllData sheet. And you must do it from another excel sheet.

Alf

[Alf]

Quick question, the AllData sheets name is not "AllData Sheet.xlsx" by the way. If so then macro will not work. In this case rename "AllData.xlsx" to "AllData Sheet.xlsx" in the macro and see if this does work.

Alf

[jimmisavage]

Hi Alf,
Here's the macro to open and close:

Please Login or Register  to view this content.

Yes, I have changed the file name in the macro as well. I know it opens the AllData sheet because I can see it open and it pasts the data into it.

Please Login or Register  to view this content.

[Alf]

Thanks for uploaded files. It's back to the drawing board. Will do my best to fix this problem during the weekend.


Alf

[Alf]

Not sure what is happening but running the macro and when it reaches the line

Please Login or Register  to view this content.

it then jumps to UDF

Please Login or Register  to view this content.

and this of course screws up everything. As I don't know what causes this behavior I have no idea how to solve it. Sheet2 looks like this after the macro hit the line:

aMacro.jpg

So I'm sorry but this problem I can't help you with.

Alf

[jimmisavage]

So I'm sorry but this problem I can't help you with.

Alf

Hi Alf,
Thanks for taking the time to help - I appreciate it.

I have tried to run the code on another worksheet (in case my worksheet was causing a problem) but it still doesn't work as expected.

If anyone else is able to chip in it would be nice to get to the bottom of what's happening here.

Many thanks
Jim

[jimmisavage]

Can anyone else offer an alternative? I don't want to give up on this one

[Alf]

I did find a solution to this problem. If you do copy range A7:A8 and paste it back as values there are no references to the UDF function and then the macro seems to work as expected. Well you better test this properly.

I did make some changes to the macro setup. First I modified the "Private Sub Workbook_BeforeSave" macro as this gave me a chance to do a better trouble shooting of the rest of the macro.

The two macros now look like this:

Please Login or Register  to view this content.

this macro goes in the ThisWorkbbok same as your "Sub Workbook_Open" macro. Then there is the "UpdateFil" macro

Please Login or Register  to view this content.

and this you can put in your modul1 where the "Function FindWord" is placed.

The column A is now formatted as date, don't know what kind of date format you use so I guesstimated yyyy-mm-dd but this you can change to mm-dd-yyyy if this is better.

The modification of A7:A8 (copying and pasting) can be add at the start of the macro.

What you also could do is to post a new thread in the forum with the title like "UDF funktion messes up my macro" as a new thread always attracts more attention than an old one where there has been some activity and hopefully a more knowledgeable forum member could find a better solution.

Alf

[Alf]

Had some problems closing the "Jimmie Savage" file so in the end I had to settle for this macro:

Please Login or Register  to view this content.

This works but the "Application.Quit" part closes excel as well, a real pain. Tried to use "ThisWorkbook.Close SaveChanges:=False" instead of "Application.Quit" and that seems also to work, just closes the file but does not close excel as well but this line does not get saved so the next time you open the file this line is gone,

Alf

[jimmisavage]

Alf, thank you so much for giving it another go! It's working perfectly now

[Alf]

You are welcome and thanks for the feedback.

Glad to hear it's working properly now. Still I would really like to know how come the UDF manages to "mess up" the maco. Not sure I'm giving up on this part of the problem yet

Alf