running a macro in a macro

[royboy531]

I am trying to record a macro that includes a userform to filter data and then a code to select data . After I record the macro involving all the steps that I need and then try to run the recorded macro it doesn't seem to work. Here is the code I have for the userform (its sorts info for date and shift) and the code for selecting data. I am new at this and don't know much about writing code so the record method seemed the way to go. Does anyone know why it might not be working.

[Private Sub btnFilter_Click()
Dim startDate As String
Dim shift As String

'reads date from the userform
startDate = tbstartdate.Value 'tbStartDate is the name of the textbox for Starting Date
shift = tbshift 'tbshift is the name of the textbox for shift

'FILTERS
With Worksheets("Nov2005")
.AutoFilterMode = False
.Range("a11").AutoFilter
.Range("a11").AutoFilter Field:=2, Criteria1:=shift
.Range("a11").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _
Operator:=xlAnd, Criteria2:=("<=" & startDate)

End With
End Sub]

and the code to select the data-

[Sub macro1()
Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row).Select
End Sub
]

just in case I was not clear when i record the macro I start at the spread sheet open VB run the userform (filters data), close userform manually (haven't got that code quite figured out yet), run the select code, close VB, copy selected data and paste it on another sheet. I hope that makes sence. any help I can get would be appriciated. thanks
royboy531

 

[acw]

Roy

Try (untested)

Private Sub btnFilter_Click() 
Dim startDate As String 
Dim shift As String 

'reads date from the userform 
startDate = tbstartdate.Value 'tbStartDate is the name of the textbox for Starting Date 
shift = tbshift 'tbshift is the name of the textbox for shift 

'FILTERS 
With Worksheets("Nov2005") 
.AutoFilterMode = False 
.Range("a1:p1").AutoFilter 
.Range("a1:p1").AutoFilter Field:=2, Criteria1:=shift 
.Range("a1:p1").AutoFilter Field:=1, Criteria1:=(">=" & startDate), _ 
Operator:=xlAnd, Criteria2:=("<=" & startDate) 

End With

Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row).copy destination:=sheets("sheet2").range("a1")

unload userform1
 
End Sub

It assumes that the current form is Userform1, the output is going to sheet2, nothing is done with any existing output that may be in that area.


Tony

 

[royboy531]

thank you acw. that worked

royboy531