×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Macro - VBA for selecting sheet based on cell value and moving them to a new workbook

Macro - VBA for selecting sheet based on cell value and moving them to a new workbook

Macro - VBA for selecting sheet based on cell value and moving them to a new workbook

(OP)
Hello all VBA pros out there. I've never realised splitting a workbook up into multiple workbooks can be so difficult and I'm at my wits end.

Would actually like to find out if the below is actually humanely possible.

What I'm trying to do:
- Split a workbook with multiple tabs up based on the criteria that cell B1 is the same
- Note that B1 is NOT the same value in all sheets and I would like a macro to be generated based on all the variables. For example, B1 = A in one new workbook (contains multiple sheets with B1 = A on original workbook. Then B1 = B in another new workbook (once again contains multiple sheets with B1 = B) and so on and so forth.

How I'm trying to go about it
- Write a VBA code that selects all sheets that B1 = A
- Move these selected worksheets to a new workbook and make sure that it's "paste values". Original file unaffected.
- Rinse and Repeat for B1 = B, B1 = C etc.

Any simpler ideas is definitely welcome as well.

I've had done some research and found 2 codes that might be useful, however it doesn't do exactly what I require.
https://www.tek-tips.com/viewthread.cfm?qid=849130
https://stackoverflow.com/questions/43763372/edit-vba-to-paste-multiple-sheets-as-values-into-new-workbook

Thanks very much in advance.

RE: Macro - VBA for selecting sheet based on cell value and moving them to a new workbook

Hi,

I am always skeptical of the reasons why one might want to chop up data into different files, different sheets, different tables. It makes accessing and analyzing the data, much MUCH, MUCH more difficult!

But I see, as I read, that the original is left unchanged. So I'll take a stab at it in the next post.

Your explanation is not clear. The value in B1 is supposed to be the new file name. Are you saying that SOME the values in B1 on each sheet are identical and if so should all be put into the new workbook by the same name and if different, then in new workbook(s) corresponding to the B1 value?

It would help if you were to upload a sample workbook that illustrates your situation.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Macro - VBA for selecting sheet based on cell value and moving them to a new workbook

(OP)
Hello Skip!

Thanks so much for taking interest in this. Here is a simplified sample of the file attached.

I really wish I don't need this code too but it's mainly for confidentiality purposes and easy dissemination.

//Your explanation is not clear. The value in B1 is supposed to be the new file name. Are you saying that SOME the values in B1 on each sheet are identical and if so should all be put into the new workbook by the same name and if different, then in new workbook(s) corresponding to the B1 value?//

On this, is it possible to have the new file name as "Original file name - Cell B1"?
Yes, 4 sheets can have the same B1 - these 4 sheets in one new excel workbook, then 2 sheets the same B1 - these 2 sheets in a new excel workbook, etc.

Would it cause a problem if I have other sheets in the workbook as well that doesn't require it to be split? I don't mind if the macro splits it to a new workbook, I can just delete the additional file created.

Let me know your thoughts once you have a go!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close