How to flatten SELECTED worksheets in my Excel file.

Help101

New Member
Joined
Apr 24, 2014
Messages
30
I hope someone can help me. I need the code on how to flatten SELECTED worksheets (not all) in my Excel file. I have more than 150 worksheets to flatten and the number of worksheets that need to be flattened will vary according to the data entered on the first worksheet. I do not know how to write codes yet and this file that I am using already has an existing code.

1) How do I write the code to flatten selected worksheets?
2) Where do I insert the code in the existing VBA script?

A similar question was asked and answered before but this question involved ALL worksheets. Here is the link to that question.

http://www.mrexcel.com/forum/excel-questions/519398-macro-flatten-worksheets.html

Thanks for your help.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Here is VBA code that will do that:
Code:
Sub FlattenWorksheets()

    Dim wks As Worksheet

    For Each wks In ActiveWindow.SelectedSheets
        With wks
            wks.UsedRange.Value = wks.UsedRange.Value
        End With
    Next wks

End Sub
One way to get to where you need to go to place the VBA code is record a new macro, stop the recorder, then bring up your list of macros, highlight the one you just recorder, and paste this code over top of that.
 
Upvote 0
Thanks Joe4.

I found the following in the existing code which I think refers to the range of worksheets that need to be flattened. Firstaccount refers to the first worksheet and lastaccount refers to the last worksheet that needs to be flattened:

firstaccount = Sheets("Accounts").Cells(1, 1).Text
lastaccount = Sheets("Accounts").Cells("" & lastrow + 1, 2).Text

1) How do I include that in your code?
2) Where do I attach it in the existing code?

Thanks again.
 
Last edited:
Upvote 0
Joe4, I copy-pasted your code above and also tried the suggestion from the link above but it did not work.
 
Upvote 0
It worked for me. Did you have all the sheets you wanted it applied to selected when you ran the Macro?
Did you put the VBA code in a standard Module, or a Worksheet module?
 
Upvote 0
Hi Joe4,

As for your question on whether it was on Standard or Worksheet Module when I appended the code to the existing VBA script (which did not work), I really could not tell you. I checked the info on the left side of the Developer screen and I just do not know enough what to look for.

I decided to experiment by creating a macro through copy-pasting your code and using a test file to run it. I thought I could control which worksheets to flatten by starting with the open worksheet in the middle of the workbook. However, ALL worksheets got flattened even those preceding the open one.

I'm thinking that I will copy-paste back the worksheets that shouldn't have flattened unless there is a workaround to this.

Thanks.
 
Upvote 0
How exactly are you selecting the Worksheets you want to apply this to?
Do you actually have each worksheet selected, or do you have a range on your Account sheet that has the names of the the sheets that you want flattened (and that is what you are selecting)?
 
Upvote 0
I apologize, I should have been clearer from the get go. I do not know how to select the worksheets. The worksheets are not static. Data is entered on the first worksheet which correspond to different account names (people). Then a macro button is pressed and this creates a separate file with all the new worksheets. Each worksheet is automatically named after each account. In the script, the only reference to the "range of accounts" is the following:

"firstaccount = Sheets("Accounts").Cells(1, 1).Text
lastaccount = Sheets("Accounts").Cells("" & lastrow + 1, 2).Text"

I think the above refers to the "dynamic range of accounts" but I do not know how to utilize the above to "select" the specific worksheets to flatten. I hope I am clearer this time.

Thanks.
 
Upvote 0
I can't say that I am following. Are you saying that there are multiple workbooks at play here?
Without seeing the entire VBA code, sample data, and maybe working through an actual example, it is difficult to visualize.
 
Upvote 0
I work with File 1 which has one worksheet where all client data (labeled as "Accounts") are entered. There are 8 other worksheets in File 1. This file is loaded with macros. There is a macro button which when clicked the following happens simultaneously.

1) File 2 is created automatically.
2) The data entry worksheet ("Accounts") and the 8 other worksheets in File 1 are copied automatically to File 2.
3) Individual worksheets corresponding to all the clients are generated and are labeled automatically with client unique identifiers entered in File 1. The number of generated client worksheets correspond to the number of clients entered in the data entry or "Accounts" worksheet in File 1.
4) All other data pertaining to the individual clients are transferred automatically to the appropriate worksheet in File 2.

My challenge is that I want to flatten only the client worksheets in File 2. However, I would like to keep the data entry worksheet ("Accounts") and the 8 other original worksheets that were copied automatically to File 2 as is (not flattened). As I have mentioned before, the only reference that I can see in the VBA script that seems to pertain to the range of client worksheets created in File 2 is the following:

"firstaccount = Sheets("Accounts").Cells(1, 1).Text
lastaccount = Sheets("Accounts").Cells("" & lastrow + 1, 2).Text"

What I have observed is that if I copy your code at the end of the existing VBA script and then run the flatten macro, ALL the worksheets in File 2 get flattened. Obviously, this is because I did not and do not know how to delimit the range of worksheets I want flattened. This, however, is better than flattening each client worksheet individually (about 100-150 worksheets depending on the number of clients entered). If there is no automatic way of selecting and flattening the client worksheets only, I am happy with your code. But if there is, I'd be happy to be shown how to write the code to delimit/select the range of worksheets to flatten.

Thanks.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top