VBA - Delete sheets in activeworkbook

[honger]

Hello All, i have also posted this at MrExcel because i kind of need a solution fairly quick and i hoped to better my chance by posting at 2 sites.

http://www.mrexcel.com/forum/excel-q...ml#post3801354
I am working on a macro which creates a new workbook by copying activeworkbook and while doing so delete some sheets.

For some reason, i can see that it deletes it, but then it creates it again. In question is highlighted in red font. Can someone point to me what i am doing wrong?

Please Login or Register  to view this content.

[alansidman]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

http://www.mrexcel.com/forum/excel-q...eworkbook.html

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

[AlphaFrog]

I am working on a macro which creates a new workbook by copying activeworkbook and while doing so delete some sheets.

For some reason, i can see that it deletes it, but then it creates it again. In question is highlighted in red font. Can someone point to me what i am doing wrong?

I don't see any code where you copy the active workbook.

You open a workbook file and delete Sheet7 in that opened workbook,do other formatting, then save it.

You then close the workbook that contains the code (not the opened workbook).


I'm not sure what you want done?

[honger]

sorry i was in rush and i was hoping for a better chance to get a solution... i have modified the first post, does that comply? if not please let me know. thank you

[alansidman]

Honger: Yes, it is now in compliance, but please read the link I provided on why it may backfire on you when you post to multiple sites.

[honger]

Below is the entire code i have.

It is supposed to copy the open active workbook and saveas in a different location. While doing so i want some sheets to be deleted according to value chosen in combobox3. Everything works but it wont delete that sheet while creating the new workbook. It does delete it, but then it gets created again . I think i know this is happening because when i got rid of the Application.Displayalerts, diaglog box asked to confirm the delete and it deleted the sheet for a second but it recreated it

Please Login or Register  to view this content.

[honger]

Honger: Yes, it is now in compliance, but please read the link I provided on why it may backfire on you when you post to multiple sites.

i completely understand, i am very new to VBA and was in need of a solution quick...i am also ancxious to figure out this stuff because this is fascinating to me.

[AlphaFrog]

You cannot use a worksheet's Code name (Sheet7) to refer to a sheet in another workbook. Sheet code names always refer to the same workbook that contains the code. You can only reference sheets in another workbook by Tab-name or index number.

This gets the Tab-name of Sheet7 from the original workbook and uses that tab-name to delete the worksheet in the copy.

Please Login or Register  to view this content.

[honger]

thank you!!! that worked like a charm!!! you guys are great.

I am not quite done yet and there is another thing i want to do, so i am going to give it a try now!!