running a macro in a macro

royboy531

Board Regular
Joined
Nov 11, 2005
Messages
52
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("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
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Roy

Try (untested)
Code:
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
 

Forum statistics

Threads
1,078,068
Messages
5,338,018
Members
399,195
Latest member
cmdv

Some videos you may like

This Week's Hot Topics

Top