Run-time error '1004'

[Fishleg]

Run-time error '1004' Select Method of Range Failed

Hi,

I would appreciate any help I get with this error message. I am
trying to copy cells in a worksheet to another worksheet in the same
workbook. I created the code with macro and need to assign a command
button to this code.
The row Range ("B53:B58"). Select is highlighted in yellow. I am a
novice user so haven't got a clue of what this is.
Please see code below:

Private Sub CommandButton1_Click()
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=26
Range("B53:B58").Select
Selection.Copy
Sheets("Shell").Select
Range("C10").Select
ActiveSheet.Paste
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=-7
Range("B24:B29").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Shell").Select
Range("D10").Select
ActiveSheet.Paste
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=-19
Range("B2:B7").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Shell").Select
Range("E10").Select
ActiveSheet.Paste
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=32
Range("B46:B51").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Shell").Select
Range("F10").Select
ActiveSheet.Paste
End Sub

Thanks in advance!

 

[Don Guillett]

sub doit()
Sheets("Data").Range("B53:B58").Copy Sheets("Shell").Range("C10")
Sheets("Data").Range("B24:B29").Copy Sheets("Shell").Range("D10")
Sheets("Data").Range("B2:B7").Copy Sheets("Shell").range("E10")
Sheets("Data").Range("B46:B51").Copy Sheets("Shell").Range("F10")
End Sub
or

with Sheets("Data")
.Range("B53:B58").Copy Sheets("Shell").Range("C10")
.Range("B24:B29").Copy Sheets("Shell").Range("D10")
.Range("B2:B7").Copy Sheets("Shell").range("E10")
.Range("B46:B51").Copy Sheets("Shell").Range("F10")
end with

 

[JLGWhiz]

You might already know this, but since you said you are a novice, I thought I
would
throw it in just in case.

For the two sets of code that Don gave you. The one titled "doit" can be
put in the standard module and called from your button with:

Private Sub CommandButton1_Click()
doit
End Sub

Or the second one can be put directly into the button code module as:

Private Sub CommandButton1_Click()
with Sheets("Data")
.Range("B53:B58").Copy Sheets("Shell").Range("C10")
.Range("B24:B29").Copy Sheets("Shell").Range("D10")
.Range("B2:B7").Copy Sheets("Shell").range("E10")
.Range("B46:B51").Copy Sheets("Shell").Range("F10")
end with
End Sub

 

[Fishleg]

You might already know this, but since you said you are a novice, I thought I
would
throw it in just in case.

For the two sets of code that Don gave you. The one titled "doit" can be
put in the standard module and called from your button with:

Private Sub CommandButton1_Click()
doit
End Sub

Or the second one can be put directly into the button code module as:

Private Sub CommandButton1_Click()
with Sheets("Data")
.Range("B53:B58").Copy Sheets("Shell").Range("C10")
.Range("B24:B29").Copy Sheets("Shell").Range("D10")
.Range("B2:B7").Copy Sheets("Shell").range("E10")
.Range("B46:B51").Copy Sheets("Shell").Range("F10")
end with
End Sub







- Show quoted text -

Thanks so much for your help Don Guillet and JL Gwhiz it worked!

 

[Don Guillett]

Glad to help. As you can see there is no need to select the sheet.

 

[Fishleg]

Glad to help. As you can see there is no need to select the sheet.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- Show quoted text -

Hi,
Wonder if you can help me with another problem I am having with the
same workbook you previously helped me with. I have forecast figures
already entered from January to December. I want users to click on a
button to display data for a certain month and not for the whole year.
I would prefer users to click on the same button as you previously
helped me witrh I have already used 12 new colums to add the YTD
figures. I used this code, "Range("M46:M51").Copy
Sheets("Shell").Range("K10")" to copy data from the cells with the YTD
figures, this #ref# appeared in the cells instead. I guess the code
does not copy data from cells with formular in it.

Any help offered would be appreciated.

Thanks in advance!

 

[Tweedy]

--
Ray Tweedale
All-around-nice-guy

sub doit()
Sheets("Data").Range("B53:B58").Copy Sheets("Shell").Range("C10")
Sheets("Data").Range("B24:B29").Copy Sheets("Shell").Range("D10")
Sheets("Data").Range("B2:B7").Copy Sheets("Shell").range("E10")
Sheets("Data").Range("B46:B51").Copy Sheets("Shell").Range("F10")
End Sub
or

with Sheets("Data")
.Range("B53:B58").Copy Sheets("Shell").Range("C10")
.Range("B24:B29").Copy Sheets("Shell").Range("D10")
.Range("B2:B7").Copy Sheets("Shell").range("E10")
.Range("B46:B51").Copy Sheets("Shell").Range("F10")
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Tweedy]

Don, I have the same problem but I am using the Range(Cell(1,1), Cell(1,1))
reference. Would apprecaite knowing why/what I'm doing wrong?
~~~~~~~ Code Snipet ~~~~~~~~~~~~~~~~~~~~~~~~~
While (CurrentCountry = OldCountry And Selection <> 0) 'see if a new
country
Sheets("MainFormLink").Range(Cells(RowIndex, 1)).Copy Sheets
_("YEPlacement").Range(Cells(RowIndex, 1))
RowIndex = RowIndex + 1 'move to next row
OldCountry = CurrentCountry ' save name for compare to
next row
Cells(RowIndex, 1).Select 'select first cellWend

 

[Dave Peterson]

I'm not Don, but this line jumps out.

Sheets("MainFormLink").Range(Cells(RowIndex, 1)).Copy _
Sheets("YEPlacement").Range(Cells(RowIndex, 1))

I'd try:

Sheets("MainFormLink").Cells(RowIndex, 1).Copy _
Sheets("YEPlacement").Cells(RowIndex, 1)

I didn't see where you were actually using range(cells(),Cells()) in any of your
posted code.

 

[Tweedy]

Dave,

Sorry I copied wrong code. I was experimenting with DOn's code and that is
what I was sending you. To phrase my problem as a question, I simply want to
copy a range of cells in one worksheet that the code finds to meet a criteria
and paste special to another worksheet in teh same workbook. I keep getting
the Run time 1004 error using the Cell(1,1) notation for the Range.

Here's what I am trying to do:

1. I have a sheet with data links (data)
2. I scan this list looking for changes to a row
3. when I get a row with changes I need to paste the value(don't want link
to pass
thru)(paste special, value)

I'ved used several code examples I found and liked Don's because of not
having to actually switch back and forth between the sheets
to the (update) sheet. I can't get past the Cell(1,1) notation problem.

 

[Dave Peterson]

Without seeing your current code, my only guess is to fully qualify each range.

You could use:

Dim RngToCopy as range
dim DestCell as range
with worksheets("Somesheetnamehere")
set rngtocopy = .range(.cells(1,1), .cells(5,4))
end with

with worksheets("receivingsheetnamehere")
set destcell = .cells(242,123)
'excel will resize the receiving range to match the sending range
end with

Rngtocopy.copy
destcell.pastespecial paste:=xlpastevalues

====
another option to retrieve the value:

destcell.resize(rngtocopy.rows.count,rngtocopy.columns.count).value _
= rngtocopy.value

=========
Untested, uncompiled. Watch for typos.

 

[Tweedy]

Dave,

Thank you so much. The code worked. I only had to change the set destcell
reference since I needed to have the next set of data pasted to the next row.
(The complete code scans thru a list incrementing as it goes)

I just used the same value as the set rngtocopy.

Thanks again for the help. As a reward to you, know that I am striving to
be more than a copy and paste programmer and someday I'll be helping people
here too. I apreciate all the help I get here that helps me do my volunteer
work more efficiently.

 

[Dave Peterson]

Glad you got it working ok.

And remember, if you always qualify your ranges, you'll be able to just copy and
paste your code <vbg>.

Dave,

Thank you so much. The code worked. I only had to change the set destcell
reference since I needed to have the next set of data pasted to the next row.
(The complete code scans thru a list incrementing as it goes)

I just used the same value as the set rngtocopy.

Thanks again for the help. As a reward to you, know that I am striving to
be more than a copy and paste programmer and someday I'll be helping people
here too. I apreciate all the help I get here that helps me do my volunteer
work more efficiently.