Copy a mamed range in one worksheet to the first blank cell in a c

[Philip J Smith]

Hi.

I have a summary report by country that is updated weekly.

I want to copy the summary data and paste them as values in the first blank
cell
in a another worksheet.

I have developed the following piece of code

Sub MoveWeeklyData()
'
Application.Goto Reference:="TotalTop"
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Worksheets("Weekly Data").Range("w_Total").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End Sub

TotalTop := a single cell named range at the top of the column I want the
first
cell in the named range to start at.

w_Total is the named range I want to copy.

The macro stops at
Worksheets("Weekly Data").Range("w_Total").Select

With the message

Run time error '1004'

Select method of range class failed.

I would be grateful is some-one could point out the error in the code.

 

[Sam Wilson]

sub try_this()

Worksheets("Weekly Data").Range("w_Total").copy
Worksheets("WhereTotalTopIs").range("TotalTop").end(xldown).offset(1,0).pastespecial paste:=xlpastevalues

end sub

 

[Don Guillett]

Reason being you cannot do it this way.
Worksheets("Weekly Data").Range("w_Total").Select
Either

Worksheets("Weekly Data").select
Range("w_Total").Select
or
application.goto Range("w_Total")
or, as suggested, do NOT select

 

[Philip J Smith]

Hi.

I tried this, I got an error message "Compile error: Syntax error"

Any ideas.

 

[Philip J Smith]

Many thanks.

Reason being you cannot do it this way.
Worksheets("Weekly Data").Range("w_Total").Select
Either

Worksheets("Weekly Data").select
Range("w_Total").Select
or
application.goto Range("w_Total")
or, as suggested, do NOT select

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

 

[Philip J Smith]

Ignore last, I worked it out.

Many thanks.

sub try_this()

Worksheets("Weekly Data").Range("w_Total").copy
Worksheets("WhereTotalTopIs").range("TotalTop").end(xldown).offset(1,0).pastespecial paste:=xlpastevalues

end sub