Forum Discussion
Possible for UserForm to Output to Multiple Sheets?
Hello!!
I'm a new user of Microsoft Excel still learning the ropes and I've got a question for you guys. I have created a Call Log for myself (I'm in sales) in which I have a UserForm where I input data and it spits it out into a single excel sheet. I've been using this for a few months and it's starting to get a bit messy with one month running into the next, making tracking a bit more difficult and time consuming.
What I'm wanting to know is this: Is there a way that I can utilize the UserForm capability and have it spit data out to specific sheets depending upon the date that is entered into the "Date" field that I have on the form?
My end goal is to have twelve sheets, one for each month and to be able to input my call data and have it sorted out to it's respective sheet (calls for April, May, June, etc.).
Hopefully this all make sense. I appreciate the help!!!
PS - If ya'll know of a better way to achieve the end goal, I'd be open to hearing that as well!
15 Replies
Just use a logical VBA statement to evaluate where to put the data. ie....use criteria to pick the worksheet
If UserForm1.Textbox1.Value = "X" Then Sheets("Sheet1").Activate Elseif UserForm1.Textbox1.Value = "Y" Sheets("Sheet2").Activate Else Sheets("Sheet3").Activate End IfMatt,
Thank you for your response! Could you elaborate a bit more? I am still very new to Excel, UserForms, VBS, etc.
I appreciate your help!!
Hi
1. Specify, in which range you have data (eg. column A to B (or) A2:C150)
2. Whether you want results in specific sheet you already have (or) you want insert new sheets.
Hello!!
I will try to upload a current copy of what I've got going on right now; a blank template. (I'm running Office/Excel 2013, but had to save the file in an older format to get it to upload with the VBA stuff function)
1. I have data being entered into Columns A through E.
2. I would like results to show up in the Month-specific sheet.
Hi
1.Keep date format in "Column B". In my pc i use 'dd-mm-yyyy' format.
2. Use Macro given below.
Sub Macro1()
'
' Macro1 Macro
'Ctr = Sheets("Tracking Log").Range("B65536").End(xlUp).Row
Sheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<02-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("January").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<03-2018", Operator:=xlAnd, Criteria2:=">01-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("February").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<04-2018", Operator:=xlAnd, Criteria2:=">02-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("March").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<05-2018", Operator:=xlAnd, Criteria2:=">03-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("April").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<06-2018", Operator:=xlAnd, Criteria2:=">04-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("May").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<07-2018", Operator:=xlAnd, Criteria2:=">05-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("June").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<08-2018", Operator:=xlAnd, Criteria2:=">06-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("July").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<09-2018", Operator:=xlAnd, Criteria2:=">07-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("August").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<10-2018", Operator:=xlAnd, Criteria2:=">08-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("September").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<11-2018", Operator:=xlAnd, Criteria2:=">09-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("October").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:="<12-2018", Operator:=xlAnd, Criteria2:=">10-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("November").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseSheets("Tracking Log").Select
ActiveSheet.Range("$A$1:$F$" & Ctr).AutoFilter Field:=2, Criteria1:=">11-2018"
Rows("2:" & Ctr).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("December").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = FalseEnd Sub
