| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MACRO: Selecting "Cancel" from a drop down to copy and paste entirerow to another sheet | Excel Discussion (Misc queries) | |||
| MACRO: Selecting "Cancel" from a drop down to copy and paste entirerow to another sheet | Excel Worksheet Functions | |||
| Macro - copy a range and paste it in a new sheet 12 times | Excel Worksheet Functions | |||
| Auto copy/ paste on relevant work sheet when selecting list item | Excel Programming | |||
| Macro to copy range of cells and paste into 1 sheet | Excel Programming | |||
Linear Mode
