DRIVING ME INSANE same code wont work twice

[lecet444]

Ok I have this code works perfectly, now I want to do that within another sheet. opens this sheet up and do what this code does.

Dim d1 As String
    Dim d2 As String
 
    d1 = TextBox1.Value
    d2 = TextBox2.Value
 
 
 Range("a6:g6").AutoFilter Field:=1, Criteria1:=">=" & d1, Operator:=xlAnd _
   , Criteria2:="<=" & d2
    Dim LR  As Long
    LR = Range("B" & Rows.Count).End(xlUp).Row
    'For i = 2 To 10000
 
TextBox3.Value = Range("B7:B" & LR).SpecialCells(xlCellTypeVisible).Cells(1).Value
TextBox4.Value = Range("B" & LR)

This is how I modified it, and it just doesnt work
I get a runtime error "no cells were found"....

Dim dat1 As String
Dim dat2 As String
Dim LR As Long
dat1 = TextBox1.Value
dat2 = TextBox3.Value 
 
 
Workbooks("master sheet.xls").Activate
 Sheets("master sheet").Range("a6:m6").AutoFilter Field:=1, Criteria1:=">=" & dat1, Operator:=xlAnd _
   , Criteria2:="<=" & dat2
 
LR = Range("B" & Rows.Count).End(xlUp).Row
 
    Workbooks("monthly report.xlsm").Sheets("requisition").TextBox4.Value = _
    Workbooks("Master sheet.xls").Sheets("master sheet").Range("B7:B" & LR).SpecialCells(xlCellTypeVisible).Cells(1).Value
    Workbooks("monthly report.xlsm").Sheets("requisition").TextBox5.Value = _
    Workbooks("master sheet.xls").Sheets("master sheet").Range("B" & LR)

 

[lecet444]

Any input would be great. I've been at it for a few hours, and cant figure it out

 

[lecet444]

I'm sure there is an expert in here that could pin point the problem. I just started vba, and excel in general a few weeks ago so I'm not great. but I know people here are more than great, and could catch what I'm doing wrong. I cant get it to work for the life of me

 

[Jonmo1]

You probably need to add the book and sheet qualifier to the LR line

LR = Workbooks("Master sheet.xls").Sheets("master sheet").Range("B" & Rows.Count).End(xlUp).Row

 

[lecet444]

You probably need to add the book and sheet qualifier to the LR line

LR = Workbooks("Master sheet.xls").Sheets("master sheet").Range("B" & Rows.Count).End(xlUp).Row

I get an application-defined or object-defined error

 

[lecet444]

by the way the second part works fine if i remove the

    'Workbooks("monthly report.xlsm").Sheets("requisition").TextBox4.Value = _
    Workbooks("Master sheet.xls").Sheets("master sheet").Range("B7:B" & LR).SpecialCells(xlCellTypeVisible).Cells(1).Value

I dont know how to approach this. if i were to choose a date that starts from the first value in the data filter both would work also. only when i choose a date that is further down from the first value.

 

[lecet444]

by the way the second part works fine if i remove the

    'Workbooks("monthly report.xlsm").Sheets("requisition").TextBox4.Value = _
    Workbooks("Master sheet.xls").Sheets("master sheet").Range("B7:B" & LR).SpecialCells(xlCellTypeVisible).Cells(1).Value

I dont know how to approach this. if i were to choose a date that starts from the first value in the data filter both would work also. only when i choose a date that is further down from the first value. for example
JUne 14 would work
june 15 would return no cell value

 

[Jonmo1]

I tried to clean it up a bit...And set variables for the books to make it easier to read..

Assuming the textboxes are from the Control Toolbox, and are on the Sheet Requisition within the book containing this code.

try

Sub test()
Dim d1 As String
Dim d2 As String
Dim LR  As Long
Dim Bk1
Dim Bk2
 
Set Bk1 = ThisWorkbook.Sheets("requisition")
Set Bk2 = Workbooks("master sheet.xls").Sheets("master sheet")
 
d1 = Bk1.TextBox1.Value
d2 = Bk1.TextBox2.Value
LR = Bk2.Range("B" & Rows.Count).End(xlUp).Row
 
Bk2.Range("A6:G" & LR).AutoFilter Field:=1, Criteria1:=">=" & d1, Operator:=xlAnd, Criteria2:="<=" & d2
 
Bk1.TextBox3.Value = Bk2.Range("B7:B" & LR).SpecialCells(xlCellTypeVisible).Cells(1).Value
Bk1.TextBox4.Value = Bk2.Range("B" & Rows.Count).End(xlUp)
End Sub

 

[lecet444]

the exact same errors, I'm getting. this is 4 hours so far, with nothing to show for lol

 

[Jonmo1]

OK, in the code I just posted...

What error do you get, on which line??

And where my assumptions correct?

Assuming the textboxes are from the Control Toolbox, and are on the Sheet Requisition within the book containing this code.