Excel CopyPasteSpecial from MS Access

[end922]

Greetings,

I am importing a spreadsheet into Access. Prior to importing I have to clean up the data a little. There are some formulas that I want copy pastespecial so I can keep the value of the cell.

When I run the code I get error PasteSpecial method of Range class failed.

Any suggestions would be appreciated.

Thanks

With oXL
        .worksheets("Aging").select
        .columns("F:F").select
        .Selection.Copy
        .Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        
        .rows("1:2").select
        .Selection.Delete Shift:=xlUP
        .columns("A:A").select
    .Selection.TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 2), TrailingMinusNumbers:=True
      .Range("A3").select

 

[AnotherHiggins]

Avoid using Select or Activate whenever possible, it just slows things down.

Try this instead:

    With oXL
        With .Worksheets("Aging")
            .Range("F:F").Value = .Range("F:F").Value
        End With
    End With

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[end922]

Cool I like it. Only one issue. It does not select sheet Aging. It runs the code on whatever sheet is seleted on time of open. When I add .select or .activate behind .worksheets("Aging") it errors out.

 

[end922]

I went ahead and added in .select before the with. Thanks for your help Anotherhiggins!!

 

[SkipVought]

If you ran Anotherhiggins' code AS POSTED, you would not need ANY Select!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[end922]

Yes he is correct. I added his code to the one I posted. That is what I was looking at.