Run-time error '1004': Select method of Range class failed.

[pspikym90]

Hello,

I have a button in one of my sheets that calls on a Sub in Module1 and the following is the code. I am using variables to set ranges, but I am getting this error. Please assist.

Sub MoveData()

Dim SheetName As String
Dim DataARange As Range
Dim DataBRange As Range

Set DataARange = Range(Range("DataA").Value)
Set DataBRange = Range(Range("DataB").Value)

Sheets("NoName1").Select
SheetName = "Paper " & Range("A19").Value
        Sheets("NoName2").Select
        Range("B20:B31").Select
        Selection.Copy
        Sheets(SheetName).Select
        DataARange.Select                                    'This is where the error occurs.
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Sheets("NoName2").Select
        Range("C20:C31").Select
        Selection.Copy
        Sheets(SheetName).Select
        DataBRange.Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True

Thanks in advance.

[Norie]

Which worksheet are DataRangeA and DataRangeB meant to be on?

[pspikym90]

They are meant to be on any of the sheets that the variable "SheetName" dictates.

[Norie]

As it is they'll be on whatever sheet is active when the code is run.

If that's not the sheet named whatever is in SheetName then the code will error.

[pspikym90]

Range("DataA").Value = B7
I don't understand why I am still getting the error. I'm sure these sheets have a range "B7"....

[Norie]

What sheet is DataA on?

What sheet is active when the code runs?

[pspikym90]

It is on a completely different sheet, but it is hidden. Could this be the problem?

[pspikym90]

I tried running the same code several times and it works half the time and gets the error the half of the time.

[pspikym90]

Nevermind. Problem persists even with the worksheet not hidden.

[Norie]

Can you attach a sample workbook?

[Norie]

The problem is because the ranges in the code have no worksheet references so they will
always refer to the active sheet.

When you run this code DataARange and DataBRange will be referring to ranges on whatever the active sheet is.

Set DataARange = Range(Range("DataA").Value)
Set DataBRange = Range(Range("DataB").Value)

The easiest way to fix the problem is to add worksheet references and remove all the unneeded Selects.

[pspikym90]

Fixed! As you said, I think it was all the unnecessary selects...

Instead of:
Range.Select
Selection.Paste

I did:
Range.Paste

So cleaning up the code helped a lot. Thanks a lot!