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
You might be getting error because of ActiveCell.Offset(-181, 0).
as from what ever current row you are on, it is saying go up 181 rows, you may not have 181 rows.
Try this. Heads up on one point. Date in excel is a funny business. You may have to play around with this part "Criteria1:="=" & Date" to get it in right format for you.
Sub Test2()
Dim lLastRow As Long
Dim wsSheet As Worksheet
Dim wsThis As Worksheet
Set wsThis = ActiveSheet
With wsThis
.AutoFilterMode = False
.Cells.AutoFilter Field:=12, Criteria1:="=" & Date
lLastRow = .Cells(.Rows.Count, 12).End(xlUp).Row
If (lLastRow = 1) Then
MsgBox "No Data found for date"
Exit Sub
End If
Set wsSheet = Sheets.Add(After:=Sheets(Sheets.Count))
wsSheet.Name = "Todays 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
This forum will not allow you to post email. you can upload the workbook at some file share site and post the link to the file back here. For what ever reasons, the forum folks like speedyshare for that purpose
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
There should be some sites for that.
I find excel macro recorder is the best. It gives you a good template to start from, Plus if you study and try to understand that code, you learn too.
has helped 1795 users this month
both refers to the column on which you want to apply filter. Since you said , next calibration date, "K" should have been "L"
Now code is working as expected
Thanks again!
I find excel macro recorder is the best. It gives you a good template to start from, Plus if you study and try to understand that code, you learn too.
http://office.microsoft.com/en-gb/training/overview-RZ001150634.aspx?section=1
http://www.excel-vba-easy.com/