menor59
04-22-2013, 02:00 PM
Hello all,
Thank you ahead of time with looking at this.
heres my code
Sub DataBaseQuote()
Call Select_Last
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Dim lr As Integer
Dim ar As Variant
Set sh = Sheet14
For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 5) = "Quote" Then
ws.Range("D7:D62").AutoFilter 1, ">0"
lr = ws.Range("C" & Rows.Count).End(xlUp).Row
If lr >= 7 Then
Sheet14.Range("A65536").End(xlUp)(2).Value = ws.Name
ws.Range("A7:F62").Copy Sheet14.Range("C65536").End(xlUp)(2)
sh.Range(sh.Cells(sh.Cells(Rows.Count, 1).End(xlUp).Row, 1), _
sh.Cells(sh.Cells(Rows.Count, 3).End(xlUp).Row, 1)).Value = ws.Name
End If
ws.AutoFilterMode = False
End If
ws.UsedRange.Calculate
Next ws
Application.Goto "startCell"
Application.ScreenUpdating = True
Application.Run "ProtectAll"
End Sub
This Works Perfectly. One problem though. on the ws in range e7:F62 there 2 formulas....
from E7
=ROUND(L7/(1-N7),2)
and from F7
=E7*D7
On the ws The calculation is correct. It puts the right result based on whats above. But when i goto Sheet 14 its putting the Formulas above...Not the Value of E7 and F7.
Thoughts Please?
Thank you!
Those Formulas above from E7 is copied from E7 to E62 and F7 is copied from F7 to F62. The Result Is a dollar amount. on the ws sheet, which is correct. But on sheet14 its not putting the result in dollars, only those formulas...from the ws.
Thank you ahead of time with looking at this.
heres my code
Sub DataBaseQuote()
Call Select_Last
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Dim lr As Integer
Dim ar As Variant
Set sh = Sheet14
For Each ws In ThisWorkbook.Worksheets
If Left(ws.Name, 5) = "Quote" Then
ws.Range("D7:D62").AutoFilter 1, ">0"
lr = ws.Range("C" & Rows.Count).End(xlUp).Row
If lr >= 7 Then
Sheet14.Range("A65536").End(xlUp)(2).Value = ws.Name
ws.Range("A7:F62").Copy Sheet14.Range("C65536").End(xlUp)(2)
sh.Range(sh.Cells(sh.Cells(Rows.Count, 1).End(xlUp).Row, 1), _
sh.Cells(sh.Cells(Rows.Count, 3).End(xlUp).Row, 1)).Value = ws.Name
End If
ws.AutoFilterMode = False
End If
ws.UsedRange.Calculate
Next ws
Application.Goto "startCell"
Application.ScreenUpdating = True
Application.Run "ProtectAll"
End Sub
This Works Perfectly. One problem though. on the ws in range e7:F62 there 2 formulas....
from E7
=ROUND(L7/(1-N7),2)
and from F7
=E7*D7
On the ws The calculation is correct. It puts the right result based on whats above. But when i goto Sheet 14 its putting the Formulas above...Not the Value of E7 and F7.
Thoughts Please?
Thank you!
Those Formulas above from E7 is copied from E7 to E62 and F7 is copied from F7 to F62. The Result Is a dollar amount. on the ws sheet, which is correct. But on sheet14 its not putting the result in dollars, only those formulas...from the ws.