Create new worksheet & copy the based on date

Solved/Closed

Raja - Apr 8, 2012 at 08:36 AM
Raja - Apr 9, 2012 at 06:25 AM

Hello,
Can any one help me to copy the excel sheet data from one sheet to another sheet?

-> I have one excel sheet (name: Test.xls, sheet name: SHEET1)

Sn Code Type next calib
5 BPR CORR 7-Apr-12
4 BPR CORR 7-Apr-12
73 BPR CORR 7-Apr-12
9 BRG CORR 8-Apr-12
10 BRG CORR 8-Apr-12
11 BRG CORR 8-Apr-12
17 BRG CORR 9-Apr-12
311 DP CORR 9-Apr-12
227 DP CORR 9-Apr-12
227 DP CORI R 9-Apr-12

Want to create a new work sheet and copy the today's date(next calib - filed name) records to new sheet.

Example: Today's Date is 8-Apr-12
So, I want to copy following record to new work sheet (when I click the button / run the macro).

Sn Code Type next calib
9 BRG CORR 8-Apr-12
10 BRG CORR 8-Apr-12
11 BRG CORR 8-Apr-12

Please help to write the code.
Thanks!

Create new worksheet & copy the based on date
Creating multiple worksheets from a template and a list of names ✓ - Forum - Excel
How to create multiple worksheets from a list of cell values - Guide
Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel
Create & name worksheets from a list and copy data ✓ - Forum - Excel
Copy a row from one worksheet to another, based on criteria ✓ - Forum - Excel

1 reply

Answer 1 / 1

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Apr 8, 2012 at 08:38 AM

Start macro recorder
Apply the filter for date
copy the visible rows
paste
stop the recorder

this will give you a good template to work from

Raja
Apr 8, 2012 at 08:42 AM

Thank you!
But, I tried above things but I cant get the correct result.

Note: I am new to the excel macro :-(

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Apr 8, 2012 at 08:43 AM

what result did u get ?
post your macro and give a bit more information about the "wrong result"

Raja
Apr 8, 2012 at 08:58 AM

I just tried to copy whole excel sheet now... its worked
but dont know how to copy the particular fields based on todays date...

used code

Sub Macro3()
Dim x As Integer
For Each cell In Selection
x = Sheets.Count
Sheets("Sheet2").Copy After:=Sheets(x)
Sheets("Sheet2 (2)").Name = cell.Value
Next cell
End Sub

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Apr 8, 2012 at 09:01 AM

it does not seem to me that you followed what I suggested earlier. Try that.

Raja
Apr 8, 2012 at 09:24 AM

I tried your suggestion...

Sub Test2()
ActiveSheet.Range("$A$2:$N$458").AutoFilter Field:=12, Operator:= _
xlFilterValues, Criteria2:=Array(2, "4/5/2012")
ActiveCell.Offset(-181, 0).Range("A1:N284").Select ' -> Getting error here
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet2").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Todays Work"
ActiveCell.Offset(12, 10).Range("A1").Select
Application.CutCopyMode = False
End Sub

Getting some error here, I wanna to copy particular date....

Note: Same thing work small data sheet.... now am trying for large data (it mean my sheet having Many data)

rizvisa1

Posts: 4479
Registration date: Thursday January 28, 2010
Status: Contributor
Last seen: May 5, 2022

769
Apr 8, 2012 at 03:54 PM

It is pretty much as before but now more tailored to your need

Sub filterByDate()
   
   Dim lLastRow            As Long
   Dim wsSheet             As Worksheet
   Dim wsThis              As Worksheet
   Dim strFilter           As String
      
   Set wsThis = ActiveSheet
   
   With wsThis
   
      strFilter = InputBox("Filter on date", "Filter Date")
      If Not (IsDate(strFilter)) _
      Then
         MsgBox strFilter & " is not a valid date"
         Exit Sub
      End If
      strFilter = Format(strFilter, .Cells(3, "K").NumberFormat)
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range(.Cells(2, 1), .Cells(.Rows.Count, .Columns.Count)).AutoFilter Field:=11, Criteria1:="=" & strFilter
      lLastRow = .Cells(.Rows.Count, 12).End(xlUp).Row
      If (lLastRow < 3) Then
         MsgBox "No Data found for date"
         Exit Sub
      End If
   
      Set wsSheet = Sheets.Add(After:=Sheets(Sheets.Count))
      wsSheet.Name = strFilter & " Work"
      Application.CutCopyMode = False
      .Range(.Cells(1, 1), .Cells(lLastRow, "N")).Copy
      wsSheet.Range("A1").PasteSpecial
      Application.CutCopyMode = False
   End With
   Set wsThis = Nothing
   Set wsSheet = Nothing
   
End Sub

Subscribe To Our Newsletter!