Forum Discussion
Possible for UserForm to Output to Multiple 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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
Sheets("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 = False
End Sub
Wow, thank you for all of that!!
Quick question, where exactly would this coding get placed? I'm still learning here. Would I create a new macro altogether or would it be incorporated into some part of the existing project? If into the existing, where?
I greatly appreciate your help!!
Ben
Hi,
Refer attached image files. And Paste Macro in Module (If you have any module paste there, it will work).
1. Right Click on Sheet Tab - > View Code.
2. Paste Macro Inside Module.
Logaraj,
Thank you so much for all of your help so far! Here are screenshots of what I did. Does it look correct? If so, how should it be responding?
I went in and opened up the UserForm and input data and it continued to perform like it did before the code.
