Not Copying the Value of an equation from range.

[menor59]

Hello all,

Thank you ahead of time with looking at this.

heres my code


[VBA]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("D762").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[/VBA]



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.

[Dave]

[VBA]
ws.Range("A7:F62").Copy Destination:=Sheet14.Range("C65536").End(xlUp)(2)

[/VBA]
HTH. Dave

[menor59]

[vba]
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("D762").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 Destination:=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
[/vba]


Heres my workbook so you can see

https://docs.google.com/file/d/0B1BS...it?usp=sharing

[SamT]

[VBA]ws.Range("A7:F62").Copy
Sheet14.Range("C65536").End(xlUp).PasteSpecial (xlPasteValues)[/VBA]

[menor59]

Run-time error '1004'
Unable to get the paste special property of the range Class.

This happens after i tab out of C7

[SamT]

You got to learn to experiment on your own.

Try removing the parentheses. This worked in my workbook.

[VBA]Sub Test()

Range("A7:F62").Copy
Sheet2.Range("A1").PasteSpecial xlPasteValues
End Sub
[/VBA]

[menor59]

Complie error

[vba]
ws.Range("A7:F62").Copy Sheet14.Range("C65536").End(xlUp).PasteSpecial xlPasteValues
[/vba]

[menor59]

I put a link to my workbook above...can you have a look?

[menor59]

SamT,

I did all that...its not about experimenting. Everythings right. I believe if you look at my code its doing a sort filter based on if theres data in D7:d62. Please have a look at my workbook if you can it will make sense if you do

[SamT]

Menor,

No everything is not right. It doesn't work.

The problem with the copy paste section starts somewhere before that code is ran.

I just spent a hour looking at all 20 code modules and it doesn't make sense. It is a pastiche of recorded macros and downloaded examples. Many procedures are duplicated in different modules with the only difference being the sheet and range worked on. Many snippets of code that accomplish the same thing are written in very different styles.

I estimate it would take me all day to refactor your code into an understandable version. It must be understandable in order to find the problems. As an example of the types of bad coding style, the one procedure that is most often called throughout the Project is declared Private, so it must be called with Application.Run and it's not even properly called then.

Only then could I begin to find the problems with that code.

I am sorry, but i am not volunteering to do that kind of work for free.

There are many here who are available for paid consulting. Why don't you contact them for help.

It is still possible that someone here will help you without charge, so don't give up.

[menor59]

SamT,

I am very greatful..believe me...i am not a coder, and yes it is from a lot of snipit coding. im sorry i inconvienced you

[SamT]

Menor,

Please, it was no inconvenience. I really enjoy coding. I only do it for pleasure, since I am medically retired. However this is a fair sized project.

If you want to learn and have the time, I would be happy to slowly go thru the code and teach you how it should be done.