Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy an array of Sheets
::Can someone help edit this code that I could copy an array of sheets onto Sheet2 please:: :: :: ::Sub copy_1():: :: Dim SourceRange As Range, DestRange As Range:: :: Dim DestSheet As Worksheet, Lr As Long:: :: :: :: With Application:: :: .ScreenUpdating = False:: :: .EnableEvents = False:: :: End With:: :: :: :: 'fill in the Source Sheet and range:: :: Set SourceRange = Sheets(\"Sheet1\").Range(\"A4:D12\"):: :: :: :: 'Fill in the destination sheet and call the LastRow:: :: 'function to find the last row:: :: Set DestSheet = Sheets(\"Sheet2\"):: :: Lr = LastRow(DestSheet):: :: :: :: 'With the information from the LastRow function we can:: :: 'create a destination cell and copy/paste the source range:: :: Set DestRange = DestSheet.Range(\"A\" & Lr + 1):: :: SourceRange.Copy DestRange:: :: :: :: With Application:: :: .ScreenUpdating = True:: :: .EnableEvents = True:: :: End With:: :: :: ::End Sub:: -- Aussiegirlone ------------------------------------------------------------------------ Aussiegirlone's Profile: http://www.thecodecage.com/forumz/member.php?userid=272 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115071 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy an array of Sheets
Aussiegirlone;413036 Wrote: ::Can someone help edit this code that I could copy an array of sheets onto Sheet2 please:: Code: -------------------- Sub copy_1() Dim SourceRange As Range, DestRange As Range Dim DestSheet As Worksheet, Lr As Long With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("Sheet1").Range("A4:D12") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet = Sheets("Sheet2") Lr = LastRow(DestSheet) 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange = DestSheet.Range("A" & Lr + 1) SourceRange.Copy DestRange With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -------------------- Aussiegirlone, what is it you want to do?, do you want to copy a last line of each and every sheet to a specific sheet, or do you want to copy the whole contents of each and every sheet to a specific sheet? -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115071 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy an array of Sheets
I have managed to change this line: Set SourceRange = Sheets("Sheet1").Range("A4:D12") to this line below Set SourceRange = Range("A:D" & LastRow(ActiveSheet)) but I only want the last line copied please! Thank you -- Aussiegirlone ------------------------------------------------------------------------ Aussiegirlone's Profile: http://www.thecodecage.com/forumz/member.php?userid=272 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115071 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy an array of Sheets
Hi Aussiegirlone
Try this: Set SourceRange=Range("A" & LastRow(ActiveSheet) & ":D" & LastRow (ActiveSheet)) Regards, Per On 12 Jul., 03:25, Aussiegirlone <Aussiegirlone. wrote: I have managed to change this line: Set SourceRange = Sheets("Sheet1").Range("A4:D12") to this line below Set SourceRange = Range("A:D" & LastRow(ActiveSheet)) but I only want the last line copied please! Thank you -- Aussiegirlone ------------------------------------------------------------------------ Aussiegirlone's Profile:http://www.thecodecage.com/forumz/member.php?userid=272 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=115071 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy an array of Sheets
Per Jessen It works very well; Thank you -- Aussiegirlone ------------------------------------------------------------------------ Aussiegirlone's Profile: http://www.thecodecage.com/forumz/member.php?userid=272 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=115071 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move or copy sheets doesn't copy format | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) | |||
Print Array for hidden sheets using VBA | Excel Discussion (Misc queries) | |||
Print Array for hidden sheets using VBA | Excel Discussion (Misc queries) | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions |