menor59
04-16-2013, 08:53 AM
Hello all,
Thank you for your time in reading this.
I have the following code that runs and brings data from sheets to a Data Sheet.
Option Explicit
Sub Quote()
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("D6: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:G62").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
Next ws
End Sub
Heres the problem:
When the Above code is run it isnt doing the calculation from E7:F62. For example E7 has =ROUND(L7/(1-N7),2) and E8 has =E7*D7. When the code is run its putting the Formula there on the DataQuote Sheet, and not the results.
Also Id like to make one Modification:
say I would Like to Exempt a Sheet. On Sheets Quote 1, Quote 2, Quote 3, Quote 4, and quote 5 I have a Checkbox that is Linked to R6 on each sheet so that if its Checked r6 goes true. If R6 on any of those sheets says True, Exempt that sheet or sheets from the DataQuote Sheet which is Sheet14..
Any thoughts is greatly appreciated.
Thank you!
Thank you for your time in reading this.
I have the following code that runs and brings data from sheets to a Data Sheet.
Option Explicit
Sub Quote()
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("D6: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:G62").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
Next ws
End Sub
Heres the problem:
When the Above code is run it isnt doing the calculation from E7:F62. For example E7 has =ROUND(L7/(1-N7),2) and E8 has =E7*D7. When the code is run its putting the Formula there on the DataQuote Sheet, and not the results.
Also Id like to make one Modification:
say I would Like to Exempt a Sheet. On Sheets Quote 1, Quote 2, Quote 3, Quote 4, and quote 5 I have a Checkbox that is Linked to R6 on each sheet so that if its Checked r6 goes true. If R6 on any of those sheets says True, Exempt that sheet or sheets from the DataQuote Sheet which is Sheet14..
Any thoughts is greatly appreciated.
Thank you!