VBA: Run-time Error 1004: PasteSpecial

[th3h0bb5]

Hello wonderful Excel gurus,

I have a bit of VBA code I could use help with. It's fairly straight-forward. Based on two cells, tt looks up a value in another workbook, copies it, closes that workbook, and pastes the value where I want it. However, I'm getting a 1004 error.

Here's the code:

Sub copytimezone()


Dim client As String
Dim Domain As String


client = Worksheets("Dashboard").Range("$BV$1").Value
Domain = Worksheets("Dashboard").Range("$BV$2").Value

Worksheets("Dashboard").Activate
Worksheets("Dashboard").Range("BW2").Clear
Workbooks.Open Filename:="XXXXXXX"& client & "&Domain=" & Domain & "&rs:Format=CSV"


' Select the range to copy from the csv
Range("A2").Select
Selection.Copy


'Selection.ClearContents
Application.DisplayAlerts = False
ActiveWorkbook.Close SaveChanges:=False


' Note:- Name of this file should be same as the file name.
Worksheets("Dashboard").Activate


'Copy csv data into this spread sheet at this Range
Application.ScreenUpdating = True
Worksheets("Dashboard").Range("BW2").Select
Worksheets("Dashboard").Range("BW2").PasteSpecial

The line is red is where the debugger points. After reading other threads, here's what I've tried so far:

- Putting a pause before the PasteSpecial
- Using just Paste instead of "PasteSpecial"
- Using Active Worksheet instead of specifying the worksheet

None of this has helped. Any help you can give would be glorious.

Cheers!

 

[Richard U]

try selection.paste or selection.pastespecial

 

[th3h0bb5]

No luck, I'm afraid. The same error pops up at the same line of code.

 

[Richard U]

Try clicking on "Record Macro", do it manually and see what code excel writes for you
I walked through your code on my machine and it works.

Either we are both missing something, or there may be some file corruption with one of the files you are using.

 

[th3h0bb5]

Oddly, the error only occurs part of the time, which adds to my confusion. Probably 1 in 5 times, it will run just fine.

Here's the code from performing the process manually:

Windows("XXXXXX.csv").Activate
Range("A2").Select
Selection.Copy
ActiveWindow.Close
Range("BW2").Select
ActiveSheet.Paste

This code is essentially the same thing as what I was working with, just a bit simpler. I tried replacing my code with this, and the error persists.

 

[Richard U]

That sounds like there's some corruption.

Try this:

Copy your code into notepad or notepad++ if you have it.
Delete the code module that is blowing up
Add a new code module to your project
Copy the code from notepad into the new project.


The same thing happened to me about a month ago. That fixed it for me.

 

[B___P]

did you try?

Windows("XXXXXX.csv").Activate
Range("A2").Select
Selection.Copy
ActiveWindow.Close
[COLOR=#ff0000]Worksheets("Dashboard").Activate[/COLOR]
Range("BW2").Select
ActiveSheet.Paste

It's your last code with a piece of the former

Bye