Copy paste without selecting sheet or range
 

Hi,

I have written a very short piece of code which only works when the correct
sheet is selected:

FinalRow = Worksheets("WeeklySummary").Range("B65536").End(xl Up).Row

Worksheets("WeeklySummary").Range("B" & FinalRow & ":z" & FinalRow -
52).Copy Destination:=Range("B" & FinalRow + 1)

However I would like this to work when I run the macro from a different
sheet ie sheet "WeeklySummary" isn't active.

I'm sure that I have to use the Dim Rng as Range syntax, but cannot quite
get it right.

Could someone please help

Many Thanks

Copy paste without selecting sheet or range
 

Hi

FinalRow = Sheets("WeeklySummary").UsedRange.Rows.Count

Sheets("WeeklySummary").Range("B & FinalRow & ":Z" & FinalRow).Copy
Sheets("WeeklySummary").Range("B & (FinalRow+1) & ":Z" &
(FinalRow+1)).PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )



"Richard" wrote in message
...
Hi,

I have written a very short piece of code which only works when the
correct
sheet is selected:

FinalRow = Worksheets("WeeklySummary").Range("B65536").End(xl Up).Row

Worksheets("WeeklySummary").Range("B" & FinalRow & ":z" & FinalRow -
52).Copy Destination:=Range("B" & FinalRow + 1)

However I would like this to work when I run the macro from a different
sheet ie sheet "WeeklySummary" isn't active.

I'm sure that I have to use the Dim Rng as Range syntax, but cannot quite
get it right.

Could someone please help

Many Thanks

Copy paste without selecting sheet or range
 

Maybe

Sub sonic()
FinalRow = Worksheets("WeeklySummary").Range("B65536").End(xl Up).Row
Worksheets("WeeklySummary").Range("B" & FinalRow & ":z" & FinalRow - 52).Copy
Worksheets("WeeklySummary").Range("B" & FinalRow + 1).PasteSpecial
End Sub



Mike

"Richard" wrote:

Hi,

I have written a very short piece of code which only works when the correct
sheet is selected:

FinalRow = Worksheets("WeeklySummary").Range("B65536").End(xl Up).Row

Worksheets("WeeklySummary").Range("B" & FinalRow & ":z" & FinalRow -
52).Copy Destination:=Range("B" & FinalRow + 1)

However I would like this to work when I run the macro from a different
sheet ie sheet "WeeklySummary" isn't active.

I'm sure that I have to use the Dim Rng as Range syntax, but cannot quite
get it right.

Could someone please help

Many Thanks