Hi guys,
I'm currently running the following:
Sub TransferDates()
Dim wbSource As Workbook
Dim wbDest As Workbook
Dim ws As Worksheet
Dim dtStart As Date
Dim dtEnd As Date
Set wbDest = Workbooks("End of year.xlsx")
Set wbSource = ThisWorkbook
dtStart = InputBox("Start date? (dd-mm-yyyy)")
dtEnd = InputBox("End date? (dd-mm-yyyy)")
If dtStart = 0 Or dtEnd = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each ws In wbSource.Worksheets
With ws
With .Range(.Cells(3, "A"), .Cells.SpecialCells(xlCellTypeLastCell))
.AutoFilter
.AutoFilter field:=15, Criteria1:=">=" & dtStart, Operator:=xlAnd, Criteria2:="<=" & dtEnd
.Range("A4:A5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("A2")
.Range("B4:B5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("B2")
.Range("O4:O5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("C2")
.Range("P4:P5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("D2")
.Range("S4:S5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("E2")
.Range("T4:T5000").SpecialCells(xlCellTypeVisible).Copy wbDest.Worksheets(ws.Name).Range("F2")
.AutoFilter
End With
End With
Next ws
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
However, the data isn't transferring to the new workbook and I can't quite figure out why.
Any help is greatly appreciated!
Bookmarks