move data from one sheet to another by month entered

[steve400243]

Hello, on the attached worksheet i'm trying to get the data from "Closed Business" to The "Executive" Sheet by month, when the Month is entered into cell J2, (I entered the first row in as an example) This would then save as "Closed Business for Distribution - October, 2016" , or whatever month is entered in J2, in a file to my desktop.

Also this would be going to upper management so if you have any ideas on a way to make it more appealing to look at that would be great also. This is not important, was just looking for ideas if you have any.

Thank you for any and all help.

 

[Nebu]

Hi:

This is similar to one of your earlier request
http://forum.chandoo.org/threads/co...sed-on-specific-cell-value.31933/#post-189695
You will have to just tweak the code for the the attached file in the OP.

Thanks

 

[steve400243]

Thanks Nebu, i could not find this earlier, Appreciate it.

 

[steve400243]

Can you help with tweaking the code? I'm not very advanced on codes. Please let me know.

 

[Nebu]

Hi :

Find the code

Sub test()
Application.ScreenUpdating = False

Dim Sdate As Date
Dim Edate As Date

Sheet5.Range("A8:K" & Sheet1.Cells(Rows.Count, 2).End(xlUp).Row).ClearContents
i& = Cells.Find(What:="*", _
                    After:=Range("C1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row - 1

If Sheet5.[J2] <> vbNullString Then
    Sdate = DateSerial(Sheet5.[K2], Sheet5.[J1], 1)
    Edate = DateSerial(Sheet5.[K2], Sheet5.[J1] + 1, 1)
End If

Scriterion$ = Format(Sdate, "mm/dd/yyyy")
Ecriterion$ = Format(Edate, "mm/dd/yyyy")

With ActiveSheet
.Range("A3:X" & Sheet6.Cells(Rows.Count, 3).End(xlUp).Row).AutoFilter Field:=17, Criteria1:=">=" & Scriterion, Operator:=xlAnd, Criteria2:="<" & Ecriterion
.Range("C4:C" & i).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet5.[G8]
.Range("F4:J" & i).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet5.[B8]
.Range("E4:E" & i).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet5.[H8]
.Range("K4:K" & i).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet5.[I8]
.Range("Q4:Q" & i).SpecialCells(xlCellTypeVisible).Copy Destination:=Sheet5.[J8]

End With
' Change the path as per your need
ThisWorkbook.SaveAs (ThisWorkbook.Path & "\" & Sheet5.[E2].Text & " " & Sheet5.[J2].Text & " " & Sheet5.[K2].Text & ".xlsm")

Application.ScreenUpdating = True


End Sub

Click on the run button on Closed Business tab to run the macro.

Note: I had to set up a helper column (AA) and Cell(J1) in Executive tab to get the date value since the the month name is a text.

Thanks

 

[steve400243]

Thank you Nebu, i appreciate your time putting this together. I will play around with it the next few days.