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.

sub try_this()

Worksheets("Weekly Data").Range("w_Total").copy
Worksheets("WhereTotalTopIs").range("TotalTop").end(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.
>
>
>

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 Removed)
"Sam Wilson" <(E-Mail Removed)> wrote in message
news:31801A15-7B7A-4DC8-9DDB-(E-Mail Removed)...
>
> sub try_this()
>
> Worksheets("Weekly Data").Range("w_Total").copy
> Worksheets("WhereTotalTopIs").range("TotalTop").end(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.
>>
>>
>>

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").end(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.
> >
> >
> >

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
> (E-Mail Removed)
> "Sam Wilson" <(E-Mail Removed)> wrote in message
> news:31801A15-7B7A-4DC8-9DDB-(E-Mail Removed)...
> >
> > sub try_this()
> >
> > Worksheets("Weekly Data").Range("w_Total").copy
> > Worksheets("WhereTotalTopIs").range("TotalTop").end(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.
> >>
> >>
> >>
>
>

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").end(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.
> >
> >
> >