PDA

View Full Version : Hi , how to select sheets



cvilla7174
04-29-2019, 10:08 AM
Hello everyone, can someone please help me. I created a user form where I can run a report. But base on the sheets I select so far I was able to create the user form and go to the selected sheets and create a new sheet at the end and ask what name to give it , what I need is figure out how to use the sheets that I am selecting to Create the desire report.
here is the code I have created so far .
Any ideas that canhelp me make this happen please.
Also The last sub I madedoesn’t' even work. So it's basically useless.
I was thinking aboutcreating a variable that can hold the sheets I desire to work with. but the problem is that I don't know how to do it, I read that I could use an array to hold the worksheets but when I tried, it kept on telling me error, debug , and I just didn't know how to get it fix. So far this is what I have, can someone please guide me . thanks

24157

24158

24159

大灰狼1976
04-29-2019, 06:03 PM
Hi cvilla7174!
Welcome to vbax forum.
You'd better upload an example.
I corrected some obvious mistakes.

Option Explicit

Private Sub cboWhichSheet_Change()
Worksheets(Me.cbowhichsheet.Value).Select
End Sub

Private Sub cmdrunreport_Click()

End Sub

Private Sub cmdselectsheet_Click()
Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = InputBox("Please enter the name for the report")
End Sub

Private Sub UserForm_Initialize()
Dim i As Integer
For i = 1 To Sheets.Count
Me.cbowhichsheet.AddItem Worksheets(i).Name
Next i
End Sub

Sub selectsheets()
Worksheets(Me.cbowhichsheet.Value).Select
End Sub

cvilla7174
04-30-2019, 10:50 AM
Hi, thanks a lot for cleaning up the code and correcting the mistakes. I am pretty new at this, one thing i am trying to accomplish is that once i select the sheets from the userform on the drop down menu. how can i do or what i need to write in the code in order to use the sheets i just selected to create a report with them. I know how to copy and paste the data i need the problem is that I need to run a loop because I don't know how to create the part to "grab the sheets" I selected.

I have a weekly report that is given to me and i run the report monthly, but i have all the weeks from january until present time, so the report i am trying to run the way i want to do it is basically, use the userform in order to select the sheets , but once i select those sheets i need a way to "store it somewhere" right now once I click under the button add sheets the way is written now it creates a new sheet but i need to paste the information from the sheets i selected to the new sheet and run the report .

for example the sheets that i would need to work with for the moth or april so far are basically 3. So how can I use this code to apply it to the selected sheets from user form. ?

here is the code I use to copy and paste the date to an existing sheet



Sub freeplay_report()

Sheet56.Select
Range("A1").CurrentRegion.Select
Selection.Copy
Sheet57.Select
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(3, 0).Select
ActiveSheet.Paste

Sheet54.Select
Range("A1").CurrentRegion.Select
Selection.Copy
Sheet57.Select
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(3, 0).Select
ActiveSheet.Paste

Sheet53.Select
Range("A1").CurrentRegion.Select
Selection.Copy
Sheet57.Select
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(3, 0).Select
ActiveSheet.Paste


'this are the sheets i need to work with
'i am going to copy and from each one and paste it to a new sheet
'Sheet56.Select
'Sheet54.Select
'Sheet53.Select

'now i need to create the loop

End Sub

so the part to copy and paste the date I got it , what I need to do is basically do that with whatever sheets I get to select from the userform. I have no idea how to do it .
Now the reason of why is because I am going to use the data to do a sumif that part I got it [ I thing I do] but that is the last part I need to add.

大灰狼1976
04-30-2019, 08:42 PM
Hello!
The requirements you've specified are very simple, but you'd better upload an example.
After I add the code, you'll see how to do it.
PS. In general, COPY, PASTE and SELECT are not required.


--Okami

Paul_Hossler
05-01-2019, 11:52 AM
@cvilla7174 -- I added CODE tags to set off and to format your macro

You can use the [#] to insert CODE …… /CODE tags and paste your macro in between

p45cal
05-01-2019, 12:57 PM
Uploading an example workbook with your sheets and userform would be very helpful.
Perhaps, instead of adding sheets one by one before running the report, you could allow the user to select multiple sheets at once in a listbox rather than a combobox. A listbox allows multiseletions (with tick boxes too, if you want) then the code can iterate through the user's selection(s):
24170

cvilla7174
05-01-2019, 02:03 PM
Hey thanks for the reply, this is exactly what I am looking for , give the option to select multiple sheets and then run the report.



Uploading an example workbook with your sheets and userform would be very helpful.
Perhaps, instead of adding sheets one by one before running the report, you could allow the user to select multiple sheets at once in a listbox rather than a combobox. A listbox allows multiseletions (with tick boxes too, if you want) then the code can iterate through the user's selection(s):
24170

cvilla7174
05-01-2019, 02:19 PM
hello here is the example of the file that has the userform and macro. Hopefully i can learn from you guys how to get this done.

cvilla7174
05-01-2019, 02:24 PM
i also have two more macros to insert in the userform but i just don't know how to do it or where to put them
for example once the sheets are selected i need to copy and paste the data to a new sheet that the user has the choice to name it whatever he likes.

Sub freeplay_report()

Sheet56.Select
Range("A1").CurrentRegion.Select
Selection.Copy
Sheet57.Select
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(3, 0).Select
ActiveSheet.Paste

Sheet54.Select
Range("A1").CurrentRegion.Select
Selection.Copy
Sheet57.Select
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(3, 0).Select
ActiveSheet.Paste

Sheet53.Select
Range("A1").CurrentRegion.Select
Selection.Copy
Sheet57.Select
Range("A" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(3, 0).Select
ActiveSheet.Paste


'this are the sheets i need to work with
'i am going to copy and from each one and paste it to a new sheet
'Sheet56.Select
'Sheet54.Select
'Sheet53.Select

'now i need to create the loop

End Sub


and the second macro I need to insert to the userform is a sumif , again I don't know where to put it or how to insert it


Sub sumifseveral()
'
' sumifseveral Macro
'
'
Range("E4").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('ancma 4-7-2019'!R3C2:R173C2,ancapril!RC9,'ancma 4-7-2019'!R3C5:R173C5)+SUMIF('ancma 4-14-2019'!R3C2:R188C2,ancapril!RC9,'ancma 4-14-2019'!R3C5:R188C5)+SUMIF('ancma 4-21-2019'!R3C2:R165C2,ancapril!RC9,'ancma 4-21-2019'!R3C5:R165C5)"
Range("E5").Select
End Sub

p45cal
05-01-2019, 04:08 PM
Have a look at the code in the attached.
Regardingyour sumifseveral macro, I don't know what you're trying to do, and when; does it refer to the chosen sheets, all the sheets? Is there ALWAYS a sheet called ancapril?

cvilla7174
05-01-2019, 04:48 PM
Thanks a lot, it works.
Now the sumif basically it's done from the selected sheets. so example when the macro is running and I get to select sheet1 sheet3 and sheet7 , the sumif will be from the values on columns B from the selected sheets and also another sumif will run from the values from column C from the selected sheets.
That part I don't know how to come up with the logical answer,
1.first I was thinking that a new table could be created at the top from all the data from the sheets selected and the run the sumif
2.the other idea I had was to have like a button that would run separate sum ifs one button for the the sumif on column B and another button to sumif the values from the selected sheets for column C.

I am still trying to understand the logic behind it.
So how can I run the sumif for those two columns? using separate buttons or can that be incorporated in the macro to run it without having to ask the user. ?

p45cal
05-01-2019, 04:55 PM
Again,
Is there ALWAYS a sheet called ancapril??

cvilla7174
05-01-2019, 05:19 PM
yes the ancapril sheet will always be there, and for example every month will have a sheetname assigned because lit will be like the end table combining all values from the previous weeks, see my report is based on the weekly values I get and I make the monthly report based on those values.
so for the month of May there will be two sheets one called the ancmay and the other one ancbravo may , for june ancjune and also ancbravo june and so forth until we get to December.


Again,?

p45cal
05-01-2019, 05:48 PM
See attached.

p45cal
05-01-2019, 05:54 PM
The attachment in msg#13 has changed since I first posted it.

cvilla7174
05-01-2019, 06:30 PM
I was able to open the file, the copy and paste from the selected sheets work. i don't get is where is the sumif suppose to take place. I know when selecting the sheets, there is value that is adding under it says Sum E but the next one it says Sum?? nothing happens. Maybe I am doing something wrong?

p45cal
05-03-2019, 08:37 AM
I used Sum E as a label because when I looked at your code which puts a formula in cell E4 it comes out:

=SUMIF('ancma 4-7-2019'!$B$3:$B$173,ancapril!$I4,'ancma 4-7-2019'!$E$3:$E$173)
+SUMIF('ancma 4-14-2019'!$B$3:$B$188,ancapril!$I4,'ancma 4-14-2019'!$E$3:$E$188)
+SUMIF('ancma 4-21-2019'!$B$3:$B$165,ancapril!$I4,'ancma 4-21-2019'!$E$3:$E$165)
Three SumIfs looking at 3 hard-coded sheet names. Taking just one of these:

=SUMIF('ancma 4-7-2019'!$B$3:$B$173,ancapril!$I4,'ancma 4-7-2019'!$E$3:$E$173)
The syntax for SUMIF is:
SUMIF(range, criteria, [sum_range])
I've colour-coded the corresponding arguments, from which you can see that the column which is SUMmed is column E.

The code which does the SUMIFing is in the ListBox1_Change() event handler.

I don't know what ranges the other SumIf would work on because I don't understand the likes of "the sumif will be from the values on columns B from the selected sheets and also another sumif will run from the values from column C from the selected sheets". So I left it. It's not coded-for.
However, since you say
I've been doing research on how to learn VBAI'd have expected you to examine the VBA code, and add for yourself a few lines of code to get you the result you want for the second SumIf.
Here are a couple of hints:
One line might start:
SumIf2 = SumIf2 + Application.SumIf(rngToCopy.~~
Another might start:
Me.Label4.Caption =~~
You might need a 3rd if you are going to use a different cell from cell I4 as a criterion for the SumIf calculation; that one might start:
Crit2 = Sheets("ancapril").Range~~

cvilla7174
05-04-2019, 06:30 PM
hey man thanks a lot , i will apply it and get back to you . i really appreciate you taking the time to guide me .