I need help to debug this Excel VBA copy and paste value code.

[hendrixharrison]

I may have change one of the syntax accidentally that is why this code isnt working anymore. Can anyone tell me why the paste: xlValues part is not working anymore?

It parses fine on Excel but the result is that nothing happens except for getting rid of the ODBC links. Formulas still appear on the sheets even though this code has ran.

Thanks


--------------------------------------
For Each wksht1 In Worksheets
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

With ActiveSheet.UsedRange
.Value = .Value
End With

If Application.ODBCErrors.Count = 0 Then
Do While (wksht1.QueryTables.Count >= 1)
wksht1.QueryTables(1).Delete
Loop
End If
Next wksht1

 

[hendrixharrison]

Well the code works on excel 2000

somehow selection.copy does not work anymore with excel 2003.

so how can I replace selection.copy then?

 

[TonyJollans]

Hi hendrixharrison,

Although you loop through each worksheet, you do your copy and paste special on the Selection every time, without first selecting the sheet or anything on it. It will operate on whatever you happened to have selected - I would suggest you were lucky when you ran it in 2K.

Try changing it to

[blue]For Each wksht1 In Worksheets
            [highlight]wksht1.Cells[/highlight].Copy
            [highlight]wksht1.Cells[/highlight].PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                        False, Transpose:=False
            [green]' etc.[/green][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[hendrixharrison]

Thanks Tony it works.

How did I not know that. That just boggles me that I didnt see that at all. I feel so stupid.