running multiple sheets into ThisWorkbook VBA

[asparagus]

Dear Master,

I have two different sheets on my excel file, how I can running two sheets into One thisWorkbook.
If file excel open, file excel will be running 2 worksheet together and save on one new workbook with two worksheet

Thanks Before

AsparAgus

 

[Luke M]

I don't understand your question. The ThisWorkbook module typically contains code dealing with the whole workbook. It doesn't really matter how many worksheet you have in the workbook. Can you try explaining again what it is you are wanting?

 

[asparagus]

Hi luke,

Into VBA project there are "thisWorkbook", I write my code there.
and,I have two different sheets in my file excel. I build my project just to one sheets on my excel with condition Today + 3.
I want in the one "thisWorkbook" can running function to 2 sheets together.
when file opened, the function will be run to multiple sheet and show date with Today + 3 each sheets

Thanks,

 

[Luke M]

Still confused...can you upload your workbook? If not, at least post your code for us to view?

 

[asparagus]

oke, this is my Code luke

Private Sub Workbook_Open()
  'nama of sheet
  Worksheets("NSeries").Select 
  
  Dim stDate As Long: stDate = Date + 2
  Dim stDate1 As Long: stDate1 = Date + 4
  Dim count As Integer
  Dim sh As Worksheet
  Dim wbNew As Workbook
  
  count = 0
  
  'filter data yang sesuai kondisi Today + 3
  Range("B6:B2000").AutoFilter 1, Criteria1:=">" & stDate, _
  Operator:=xlAnd, Criteria2:="<" & stDate1
  Range("A6:EW2000").Copy 'copy data yang sudah di filter
  
  Workbooks.Add 'create new workbook
  Set wbNew = ActiveWorkbook
  [A4].PasteSpecial xlPasteAll 'paste data yang di copy ke new workbook
  [A4].PasteSpecial xlPasteValues
  wbNew.Sheets(1).Name = "Monitoring List CKD N-Series" ' setting untuk mengubah nama sheet di workbook baru
  wbNew.SaveAs "D:\Users\galih\Desktop\Reminder Monitoring Lot " & Format(Now, "dd-mmm-yy") & ".xlsx"
  wbNew.Close 'close new workbook
  
  For Each cell In Range("B7:B1994") 'range cell
  If cell.Value = Date + 3 Then
  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
  count = count + 1
  Else
  End If
  Next
  ' kondisi sending email hanya 1 kali
  If count > 0 Then
  'SendReminderMail
  ElseIf count = 0 Then
  End If
  
  
  Worksheets("FSeries").Select
  
  Range("B6:B2000").AutoFilter 1, Criteria1:=">" & stDate, _
  Operator:=xlAnd, Criteria2:="<" & stDate1
  Range("A6:EW2000").Copy 'copy data yang sudah di filter
  
  Workbooks.Add 'create new workbook
  Set wbNew = ActiveWorkbook
  [A4].PasteSpecial xlPasteAll 'paste data yang di copy ke new workbook
  wbNew.Sheets(2).Name = "Monitoring List CKD F-Series" 
  wbNew.SaveAs "D:\Users\muhammad.galih\Desktop\Reminder Monitoring Lot " & Format(Now, "dd-mmm-yy") & ".xlsx"
  wbNew.Close 'close new workbook
  
  For Each cell In Range("B7:B1994") 'range cell
    If cell.Value = Date + 3 Then
    Cells(3, 2).Interior.ColorIndex = 3
    Cells(3, 2).Font.ColorIndex = 1
    Range("B3").Value = cell.Value    
   count = count + 1
  Else
  End If
  Next
  
  If count > 0 Then
  SendReminderMail
  ElseIf count = 0 Then
  End If
End Sub

 

[Luke M]

Ok, that's progress. I can see now that you have basically the same macro, but for two different sheets. Are you wanting to copy both F-series and N-series into the same new workbook?

 

[asparagus]

I'm sorry before to make you confuse. Yess luke, i want to copy both F-Series and N-series into the same new workbook. I try to use my code but not success.

Thanks,
Asparagus

 

[Luke M]

Here's a re-written macro that does the transfer to both sheet. Note that since this uses the Workbook_Open event, it will start to run right away unless you hold down Shift while opening the file.

 

[asparagus]

Wooooow....Great, thanks a lot luke.

Regards,
AsparAgus