select only some sheets from a workbook through vba code so that reports can be got from those sheet

[rajavj]

hi to all experts here

i need to select only some sheets(excel) from the workbook programmatically(vba). the names of the sheets will be varying every day. but i have to select only some of the sheets so that i can get reports from those sheets. please can some one help in this problem.

 

[SkipVought]

Hi, and welcome to Tek-Tips.

Maybe this is just a small representation of your actual data, but there is absolutely no reason to store your data in multiple sheets. That adds needless complication. The only reason to do so would be that your table would exceed the Excel maximum row count of 1,048,576, in which case, Excel would not be the tool you should use.

However, you have other problems.

Data in the column immediately to the right of your table: column W
Data in the row immediately below your table: row 61 on the first sheet.

faq68-5184

This comment is a bit more picky, but I would delete the empty rows above the table.
I would convert the table to a Structured Table.
I would position any column summaries above the table.

Hope this helps. Good luck!



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rajavj]

i think i have not conveyed my problem properly.
daily i have prepare some data as mentioned in the sample sheets.
now the workbook i am working contains at least 3 or 4 days of data consisting of 20 to 25 sheets.
now daily i have get reports from the sheets for the data entered in some particular sheets only.
i cannot say which sheets. i have to select them. so i need a solution which is somewhat a listbox which contains all the sheet names and i have to select some sheets from the listbox and i have to get reports from those sheets only.
the code i am at present using gets the data from all the sheets in the workbook.
hope i am clear to this point. and i hope some expert will solve my problem.

 

[combo]

Returning to your original question, the Select method for sheets and worksheets has an optional 'Replace' argument. Set it to True (except from first sheet to select) when you loop through sheets and find a sheet to extend selection.
From MS help: "Used only with sheets. True to replace the current selection with the specified object. False to extend the current selection to include any previously selected objects and the specified object."

The access to selection: through ActiveWindow.SelectedSheets (or other window reference).

combo

 

[Andrzejek]

I would definitely consider the above solutions, but if you do want to go with VBA, you can display the list of your worksheets in a list on the UserFoorm. Place a ListBox on a UserForm and name it [tt]lstSheets[/tt]

Private Sub FillSheetsList()
Dim intS As Integer

With lstSheets
    .MultiSelect = fmMultiSelectMulti
    .Clear
     For intS = 1 To Sheets.Count
        .AddItem Sheets(intS).Name
     Next intS
End With

End Sub

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[rajavj]

to the experts here i request to go through my code and see what is the result i am getting.
i want to select only some sheets for my report.
click the summary button on my sheet.

 

[combo]

Do you mean selecting worksheets in workbook or pages to print? In the first case select any of the tips above. In the latter one build the PrintArea range, print range, or use From and To arguments for printed range.

combo

 

[rajavj]

i think i am unable to clearly specify the problem i am facing.
right now i am using some vba code which is embedded in the commandbutton on the every work sheet.
now i enter some data for some 6 sheets today i.e.,
sheet1 sheet2 sheet3 sheet4 sheet5 sheet6 and so on.

next day i enter some more data for some another 8 sheets i.e.,

sheet7 sheet8 sheet 9 sheet10 sheet11 sheet12 and so on like that every day i have to enter some data in fresh sheets in the workbook.

now i have to get reports every day from the sheets i have entered data on that day only.
i am using some vba code which gets reports from all the sheets in the workbook.

For each ws in this workbook
......
next ws

this code gets reports from all the sheets in the worksheets.
i dont want that. i have to select some sheets in which i have entered data on the day.

for this i could not find any helpful code.

now i think i have made myself clear about the problem i am facing.


i once again request the experts here, please implement the code i am using which is there in the attachment and the problem will be clearly seen.


thanks to all experts.

 

[combo]

If it is not possible to collect data in single sheet, having additional one or two columns to identify data instead of sheets, then test each ws (name or other criteria):
[pre]For each ws in this workbook
If [check if ws is to process] Then
......
End If
next ws[/pre]

combo

 

[Andrzejek]

Since your Excel file is macro-enabled (*.xlsm), very few people will risk opening it.
Except for the data, what is the difference between sheet1 sheet2 sheet3 sheet4 sheet5 sheet6 and so on? Do they have the same structure? The same columns? If so, I would definitely use Skip's suggestion.

But...
You may do yourself a favor and - instead of naming your ws: sheet1 sheet2 sheet3 sheet4 - name them with today's date: [blue]08-19-2021[/blue] 01, [blue]08-19-2021[/blue] 02, [blue]08-19-2021[/blue] 03, etc.

"i have to get reports every day from the sheets i have entered data on that day only"

Dim w As Integer

For w = 1 To Worksheets.Count
    If InStr(Worksheets(w).Name, Format(Date, "MM-DD-YYYY")) Then
        Debug.Print Worksheets(w).Name & [blue]"    Found today's worksheet"[/blue]
    [green]    'Do your daily magic here[/green]
    End If
Next w

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

i enter some data for some 6 sheets today i.e.,
sheet1 sheet2 sheet3 sheet4 sheet5 sheet6 and so on.

next day i enter some more data for some another 8 sheets i.e.,

sheet7 sheet8 sheet 9 sheet10 sheet11 sheet12 and so on like that every day i have to enter some data in fresh sheets in the workbook.

Why do you put all this data in separate sheets? The two sheets that you showed us 14530 to 14583-RMS-TI-880 & 14584 to 14594-RMS-TI-881, have the same column field names.

This data belongs in ONE table.

It will make your life so much more simple. It is how databases are constructed. You won't need to select sheets to do your report. Its ALL IN ONE TABLE! The trouble is, you don't understand how tables work and because you don't, you think that you have to keep you data with the same headings in different sheets and THAT is what's causing you problems.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Rajavj, using the workbook that you originally uploaded as an example, tell us what data you might want to report.

We'll show you how it can easily be done with ONE TABLE.

BTW, examining your data on sheet 14530 to 14583-RMS-TI-880, looks as if you have Bill Nos outside that range like 14584 to 14593. Did you know that?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rajavj]

TO ALL EXPERTS HERE
I AM ENCLOSING THE VBA CODE WHICH I AM USING FOR GETTING MY REPORTS

 

[rajavj]

ALSO I AM ENCLOSING THE SUMMARY SHEET WHICH I AM GETTING FOR MY REPORT FROM ALL THE SHEETS.
BUT I WANT MY REPORT FROM ONLY SOME SHEETS

 

[strongm]

The cuirrently active window has a SelectedSheets collectio - it is just like the Worksheets collection, but only contains the sheets currently selected. This information may prove of some use.

 

[SkipVought]

I have taken your original workbook and modified it as follows:

1) The data on your two sheets have been COMBINED into ONE TABLE on sheet SkipVought.
2) The table on SkipVought has been made a Structured Table.
2) on the Summary Sheet I have made formulas in two semmaries of your 5 Mills for each sheet. And another summary of mills by weeks.

ALL the summaries are simple Excel summarizing functions using multiple criteria. This is not complicated. I duplicate and correct your summary by what's actually in your tables.

Separate sheets HUGELY complicate what you want to do.

A single table GREATLY simplifies and enhances your ongoing summary tasks to slice and dice your data and serve up your valuable data!

Open this workbook and see the summaries I got from your data all in one sheet. Now tell me what other summaries you need and I'll show you how to summarize with Excel formulas.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[rajavj]

I COULD NOT MAKE MY PROBLEM CLEAR TO THE EXPERTS HERE.
BAD LUCK.
JUST BEFORE THIS POST I HAVE ATTACHED MY WORKBOOK.
JUST GO THROUGH IN THE WAY I AM GOING, FOR THE PROBLEM TO BE CLEAR.


PHASE 1.
DAILY, I REPEAT, DAILY I HAVE TO ENTER DATA INTO SEPARATE SHEETS ONLY. SUPPOSE I TAKE A FRESH WORKBOOK AND ENTER DATA.
I HAVE TO PRINT THE DATA AS IT IS AND SUBMIT TO HIGHERS.
NEXT, I HAVE TO MAKE REPORTS.



NOW, I SUBMIT REPORT, SAY, BEFORE NOON. THAT IS OK FOR THE DAY.

PHASE 2.


BUT CONTINUING TILL EVENING, I GO ON MY WORK I.E., ENTERING DATA INTO SEPARATE SHEETS IN THE SAME WORKBOOK.
THE NEXT DAY, BEFORE NOON, I AGAIN ENTER SOME DATA INTO SOME MORE SHEETS.
NOW I TAKE PRINTOUTS OF THE DATA PERTAINING TO THE DATA ENTERED THE PREVIOUS DAY AND THIS DAY UPTO NOON.

NEXT, I HAVE TO MAKE REPORTS. NOW FOR THE REPORTS, I HAVE FRAMED SOME VBA AND I AM GETTING REPORTS AS USUAL.
HERE IS THE PROBLEM.

I HAVE TO GET REPORTS FROM THE SHEETS IN WHICH I HAVE ENTERED DATA THE PREVIOUS DAY UPTO NOON AND THIS DAY UPTO NOON.

BUT I HAVE ALSO SOME SHEETS IN THE SAME WORKBOOK FOR WHICH I HAVE ALREADY GOT REPORTS.

NOW THE CODE I AM USING GETS REPORTS FROM ALL THE SHEETS IN THE WORKBOOK. THIS REPORTS INCLUDES THE REPORTS ALREADY I HAVE GOT.

I HAVE TO GET REPORTS FROM THE SHEETS IN WHICH I HAVE ENTERED DATA THE PREVIOUS DAY UPTO NOON AND THIS DAY UPTO NOON.

I HAVE TO GET REPORTS FROM THE SHEETS IN WHICH I HAVE ENTERED DATA THE PREVIOUS DAY UPTO NOON AND THIS DAY UPTO NOON.

I HAVE TO GET REPORTS FROM THE SHEETS IN WHICH I HAVE ENTERED DATA THE PREVIOUS DAY UPTO NOON AND THIS DAY UPTO NOON.

THERE IS NO QUESTION OF DATE HERE.

FIRST I HAVE TO GET THE LIST SHEET NAMES INTO ANOTHER SHEET. AND I HAVE TO SELECT THE SHEET NAMES FROM THE LIST. AND GET REPORTS FROM THE SELECTED SHEET NAMES.

NOW CAN THIS BE DONE THROUGH CODE.



ONCE AGAIN REQUESTING THE EXPERTS.

 

[SkipVought]

As you can see, I included your sheet names in the BOS column on the SkipVought tab.

The first example shows the totals for those sheet names by Name of Mill and sheet.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

If you can't define any automation in recognosing sheets to process, as it was suggested, multiselect list on userform can be helpful.
Data already reported has to be excluded, so you can use sheet level names to mark such sheets, the extra management and visualisation can be in userform list too.

My final remark: people responding here understand what you are doing, esp. having the code and data samples. I'm with Skip, splitting the same data structure between sheets as a rule is a source of future problems. Sometimes rearranging the data makes the work easier, and easier responding to changed expectations of managers.

combo

 

[SkipVought]

Here's a screenshot of your Summary sheet with my summaries, the first of which shows the data you want.

You can see that the selected cell G8, containing the formula...
=SUM((Table1[[highlight #FCE94F]BOS[/highlight]]=$A8)*(Table1[[highlight #FCE94F]Name of the Mill[/highlight][highlight #FCE94F][/highlight]]=G$6)*(Table1[[highlight #FCE94F]Gr-A(QTLS)4[/highlight]]))
is using the sheet name (BOS), 14584 to 14594-RMS-TI-881 and the Name of the Mill to calculate the Gr-A(QTLS)4

Notice the calculated totals, coincide with the actual values within the table on the SkipVought tab.

My suggestion for this Summary by sheet(s) would be to do a PivotTable on the combined sheets table (my SkipVought that you would add to each day) and select the BOS (sheet names) that you want to report. I'll show that in my next post.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein