Copy Paste to New Workbook with selected value

[asparagus]

Hallo Master,

I try to build reminder using VBA excel, and in my project I have 1000 date data,i want to copy paste my data with condition Today + 3 to New Workbook With Header Master file excel before.

Lot   Date     
15 14-Nov-14
15 14-Nov-14
2 10-Sep-14
2 10-Sep-14
2 10-Sep-14

and this is my VBA code

Private Sub Workbook_Open()
  Worksheets("Monitoring List CKD NSeries").Select
  For Each cell In Range("B7:B1994") 'range cell
  
  'menentukan value dari range kolom B yang seuai dengan tanggal hari ini + 3
  If cell.Value = Date + 3 Then
  
  Range("A6:EW6").Copy
  Workbooks.Add
  Range("A4").PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  'setting warna sesuai cell
  Cells(3, 2).Interior.ColorIndex = 3
  Cells(3, 2).Font.ColorIndex = 1
  Range("B3").Value = cell.Value 'menampilkan tanggal yang sesuai dengan tanggal hari ini +3
  Application.Speech.Speak ("send reminder")
  Application.Speech.Speak (cell.Offset(0, -1).Value)
  ActiveWorkbook.SaveAs "D:\Users\muhammad.galih\Dekstop\Reminder Monitoring Lot"
  'SendReminderMail
  End If
 Next
 
End Sub

 

[Smallman]

Hi asparagus

Give the following a try.

Sub MoveDates()
Dim StDate As Long: StDate = Now + 2
 
  Range("B6:B2000").AutoFilter 1, ">=" & StDate
  Range("A6:EW2000").Copy
  Workbooks.Add
  [A4].PasteSpecial xlPasteValues
  ActiveWorkbook.SaveAs "D:\Users\muhammad.galih\Dekstop\Reminder Monitoring Lot.xls"
  ActiveWorkbook.Close False
End Sub

Tested on my machine with a different path and goes well.

Smallman

 

[asparagus]

Hi Smallman

Thanks for your respond, actually the function success copy to the path but just header Lot & Date, value date NOW + 3 doesn't copy.
When I am running appear warning message "Microsoft Excel Cannot access the file"

 

[Smallman]

Check your dates. Probably a problem with your dates. Here is the file I ran. Just ran it again and it ran very smoothly.

Make sure you check your file path to save as your method looks a bit off. You need to name the file and the path.

Take care

Smallman

 

[asparagus]

in your excel work very well, but in my excel doesn't work well
the function not read and filter column date
Please check my excel and my code.

 

[Smallman]

OK

So my excel file is a perfect test environment. I just ran the code on the first file and it ran like a dream. Might help if you quote the correct ranges. I changed the range and this is the code I used.

Option Explicit
 
Sub MoveDates1()
Dim StDate As Long: StDate = Now + 2
 
  Range("B3:B2000").AutoFilter 1, ">=" & StDate
  Range("A3:EW2000").Copy
  Workbooks.Add
  [A4].PasteSpecial xlPasteValues
  ActiveWorkbook.SaveAs "D:\Test\Testing1.xls"
  ActiveWorkbook.Close False
End Sub

File was saved nicely. Change file path to suit.

Take care

Smallman

 

[asparagus]

oooohh Amazing smallman, now the function work very well on my excel

thanks a lot for your help

 

[Smallman]

You are most welcome @asparagus

Good luck with the rest of your project.

Smallman