Copy paste without selecting sheet or range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Richard - 23 May 2008 11:27 GMT

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(xlUp).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

Reply to this Message

Arvi Laanemets - 23 May 2008 12:16 GMT

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

Signature

Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

> Hi,
>
[quoted text clipped - 16 lines]
>
> Many Thanks

Reply to this Message

Mike H - 23 May 2008 12:22 GMT

Maybe

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

Mike

> Hi,
>
[quoted text clipped - 15 lines]
>
> Many Thanks

Reply to this Message