How to flatten SELECTED worksheets in my Excel file.

[Help101]

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.

 

[Joe4]

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 <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> script that seems to pertain to the range of client worksheets created in File 2 is the following:

It sounds like all you may need to do is to identify the sheets you want flattened and loop through them.
So, if I understand you correctly, your File2 will have a data entry sheet, the 8 other original worksheets. These sheets you do not want flattened, right?
It is all the sheets AFTER these first 9 that you want flattened, right?

Then we should be able to loop through all these other sheets and flatten just them.
That block of VBA code would look something like this:

'   Make sure you are in the file you want to flatten, then run the following code

'   Flatten all sheets starting at sheet 10 and going to the last sheet
    For i = 10 To Worksheets.Count
        Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value
    Next i

 

[Help101]

Thanks so much Joe4 for all the help.

I just discovered that a summary worksheet containing formulas is also automatically added as the LAST worksheet in File 2 (it follows the last client/account worksheet). This summary worksheet should not be flattened either. Can you please refine your code to accommodate this requirement? Also, would the new code replace the longer code you provided earlier or is it to be added on after it? Thanks.

 

[Joe4]

If you want to exclude your last sheet, just add a "-1" to the end of your loop so that it only goes up through the second to last sheet.

'   Make sure you are in the file you want to flatten, then run the following code

'   Flatten all sheets starting at sheet 10 and going to the last sheet
    For i = 10 To Worksheets.Count [B][COLOR=#ff0000]- 1[/COLOR][/B]
        Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value
    Next i

 

[Help101]

It sounds like all you may need to do is to identify the sheets you want flattened and loop through them.
So, if I understand you correctly, your File2 will have a data entry sheet, the 8 other original worksheets. These sheets you do not want flattened, right?
It is all the sheets AFTER these first 9 that you want flattened, right?

Then we should be able to loop through all these other sheets and flatten just them.
That block of VBA code would look something like this:

'   Make sure you are in the file you want to flatten, then run the following code

'   Flatten all sheets starting at sheet 10 and going to the last sheet
    For i = 10 To Worksheets.Count
        Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value
    Next i

Thanks Joe4.

Taking your suggestion, I deleted the following original flatten code you provided (which flattened everything).

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

I then replaced it with the following you provided (with the -1) to avoid flattening the last automatically-created worksheet.

Sub FlattenWorksheets()

' Make sure you are in the file you want to flatten, then run the following code
' Flatten all sheets starting at sheet 10 and going to the last sheet
For i = 10 To Worksheets.Count - 1
Sheets(i).UsedRange.Value = Sheets(i).UsedRange.Value
Next i

End Sub

Please note that I sandwiched your code with Sub FlattenWorksheets()and End Sub. When I ran the macro, I got the following.

1. "Compile error: Variable not defined" error message
2. Sub FlattenWorksheets() was highlighted in yellow
3. the first i was highlighted (For i = 10 ...)

Could you please refine the code?

Thanks.

 

[Joe4]

It sounds to me like you have the "Option Explicit" setting on, which requires you to declare all variables before using them. So just add a line like this to the beginning of your code:

Dim i as Integer