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

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.

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

sub try_this()

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

end sub

"Philip J Smith" wrote:

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.

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

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

"Sam Wilson" wrote in message
...

sub try_this()

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

end sub

"Philip J Smith" wrote:

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.

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

Hi.

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

Any ideas.

"Sam Wilson" wrote:


sub try_this()

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

end sub

"Philip J Smith" wrote:

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.

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

Many thanks.

"Don Guillett" wrote:

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

"Sam Wilson" wrote in message
...

sub try_this()

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

end sub

"Philip J Smith" wrote:

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.

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

Ignore last, I worked it out.

Many thanks.

"Sam Wilson" wrote:


sub try_this()

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

end sub

"Philip J Smith" wrote:

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.