Forum Discussion

bengriffin4316's avatar
bengriffin4316
Copper Contributor
Apr 24, 2018

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 inp...
Macros & VBA
Need Help
Logaraj Sekar's avatar
Logaraj Sekar
Iron Contributor
Apr 25, 2018

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.

 

  • bengriffin4316's avatar
    bengriffin4316
    Copper Contributor
    Apr 25, 2018

    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.

    • Logaraj Sekar's avatar
      Logaraj Sekar
      Iron Contributor
      Apr 26, 2018

      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

      • bengriffin4316's avatar
        bengriffin4316
        Copper Contributor
        Apr 26, 2018

        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

Resources