Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cell range to another sheet
Hi Guys,
I am looking for some help. I have a macro that I have adapted from Ron de Bruin. Instead of setting the SourceRange explicitly I would like to code something like this: Select Sheets("List").Range("e2") then select until the column is empty and use that as my SourceRange. Sub copy_1() Dim SourceRange As Range Dim DestRange1 As Range Dim DestRange2 As Range Dim DestRange3 As Range Dim DestRange4 As Range Dim DestSheet1 As Worksheet, Lr As Long Dim DestSheet2 As Worksheet Dim DestSheet3 As Worksheet Dim DestSheet4 As Worksheet With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("Stock Summary").Range("A4:e55") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet1 = Sheets("Opening Stock") Lr = LastRow(DestSheet1) Set DestSheet2 = Sheets("Closing Stock") Lr = LastRow(DestSheet2) Set DestSheet3 = Sheets("Purchases") Lr = LastRow(DestSheet3) Set DestSheet4 = Sheets("Usage") Lr = LastRow(DestSheet4) 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange1 = DestSheet1.Range("A2") SourceRange.Copy DestRange1 Set DestRange2 = DestSheet2.Range("A2") SourceRange.Copy DestRange2 Set DestRange3 = DestSheet3.Range("A2") SourceRange.Copy DestRange3 Set DestRange4 = DestSheet4.Range("A2") SourceRange.Copy DestRange4 ' Set DestRange1 = DestSheet1.Range("A" & Lr + 1) ' SourceRange.Copy DestRange1 ' Set DestRange2 = DestSheet2.Range("A" & Lr + 1) ' SourceRange.Copy DestRange2 ' Set DestRange3 = DestSheet3.Range("A" & Lr + 1) ' SourceRange.Copy DestRange3 ' Set DestRange4 = DestSheet4.Range("A" & Lr + 1) ' SourceRange.Copy DestRange4 With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Thanks Albert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cell range to another sheet
Do you want to set the source range as a single cell? or a range of cells? Do you want the user to be able to specify the range perhaps with an inputbox or userform? What is it you are trying to do? Albert;667684 Wrote: Hi Guys, I am looking for some help. I have a macro that I have adapted from Ron de Bruin. Instead of setting the SourceRange explicitly I would like to code something like this: Select Sheets("List").Range("e2") then select until the column is empty and use that as my SourceRange. VBA Code: -------------------- Sub copy_1() Dim SourceRange As Range Dim DestRange1 As Range Dim DestRange2 As Range Dim DestRange3 As Range Dim DestRange4 As Range Dim DestSheet1 As Worksheet, Lr As Long Dim DestSheet2 As Worksheet Dim DestSheet3 As Worksheet Dim DestSheet4 As Worksheet With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("Stock Summary").Range("A4:e55") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet1 = Sheets("Opening Stock") Lr = LastRow(DestSheet1) Set DestSheet2 = Sheets("Closing Stock") Lr = LastRow(DestSheet2) Set DestSheet3 = Sheets("Purchases") Lr = LastRow(DestSheet3) Set DestSheet4 = Sheets("Usage") Lr = LastRow(DestSheet4) 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange1 = DestSheet1.Range("A2") SourceRange.Copy DestRange1 Set DestRange2 = DestSheet2.Range("A2") SourceRange.Copy DestRange2 Set DestRange3 = DestSheet3.Range("A2") SourceRange.Copy DestRange3 Set DestRange4 = DestSheet4.Range("A2") SourceRange.Copy DestRange4 ' Set DestRange1 = DestSheet1.Range("A" & Lr + 1) ' SourceRange.Copy DestRange1 ' Set DestRange2 = DestSheet2.Range("A" & Lr + 1) ' SourceRange.Copy DestRange2 ' Set DestRange3 = DestSheet3.Range("A" & Lr + 1) ' SourceRange.Copy DestRange3 ' Set DestRange4 = DestSheet4.Range("A" & Lr + 1) ' SourceRange.Copy DestRange4 With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -------------------- Thanks Albert -- Simon Lloyd Regards, Simon Lloyd 'Excel Chat' (http://www.thecodecage.com/forumz/chat.php) ------------------------------------------------------------------------ Simon Lloyd's Profile: 1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=186569 Excel Live Chat |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cell range to another sheet
On Mar 11, 4:21*pm, Albert wrote:
Hi Guys, I am looking for some help. I have a macro that I have adapted from Ron de Bruin. Instead of setting the SourceRange explicitly I would like to code something like this: Select Sheets("List").Range("e2") then select until the column is empty and use that as my SourceRange. Sub copy_1() * * Dim SourceRange As Range * * Dim DestRange1 As Range * * Dim DestRange2 As Range * * Dim DestRange3 As Range * * Dim DestRange4 As Range * * Dim DestSheet1 As Worksheet, Lr As Long * * Dim DestSheet2 As Worksheet * * Dim DestSheet3 As Worksheet * * Dim DestSheet4 As Worksheet * * With Application * * * * .ScreenUpdating = False * * * * .EnableEvents = False * * End With * * 'fill in the Source Sheet and range * * Set SourceRange = Sheets("Stock Summary").Range("A4:e55") * * 'Fill in the destination sheet and call the LastRow * * 'function to find the last row * * Set DestSheet1 = Sheets("Opening Stock") * * Lr = LastRow(DestSheet1) * * Set DestSheet2 = Sheets("Closing Stock") * * Lr = LastRow(DestSheet2) * * Set DestSheet3 = Sheets("Purchases") * * Lr = LastRow(DestSheet3) * * Set DestSheet4 = Sheets("Usage") * * Lr = LastRow(DestSheet4) * * 'With the information from the LastRow function we can * * 'create a destination cell and copy/paste the source range * * Set DestRange1 = DestSheet1.Range("A2") * * SourceRange.Copy DestRange1 * * Set DestRange2 = DestSheet2.Range("A2") * * SourceRange.Copy DestRange2 * * Set DestRange3 = DestSheet3.Range("A2") * * SourceRange.Copy DestRange3 * * Set DestRange4 = DestSheet4.Range("A2") * * SourceRange.Copy DestRange4 ' * *Set DestRange1 = DestSheet1.Range("A" & Lr + 1) ' * *SourceRange.Copy DestRange1 ' * *Set DestRange2 = DestSheet2.Range("A" & Lr + 1) ' * *SourceRange.Copy DestRange2 ' * *Set DestRange3 = DestSheet3.Range("A" & Lr + 1) ' * *SourceRange.Copy DestRange3 ' * *Set DestRange4 = DestSheet4.Range("A" & Lr + 1) ' * *SourceRange.Copy DestRange4 * * With Application * * * * .ScreenUpdating = True * * * * .EnableEvents = True * * End With End Sub Thanks Albert Something like this? With Sheets("List") lr = .Range("e2").End(xlDown).Row Set sourcerng = .Range("e2:e" & lr) End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cell range to another sheet
Hi Simon,
All I want to do is create a range and paste it to another worksheet. The range is dynamic as if the list changes the the selection range will change. So the selection to copy will start in cell "E2" and stop at the end of the data in column "E" ( a soon as there is a blank cell which will denote the end of the range) Hope that helps. Thanks Albert "Simon Lloyd" wrote: Do you want to set the source range as a single cell? or a range of cells? Do you want the user to be able to specify the range perhaps with an inputbox or userform? What is it you are trying to do? Albert;667684 Wrote: Hi Guys, I am looking for some help. I have a macro that I have adapted from Ron de Bruin. Instead of setting the SourceRange explicitly I would like to code something like this: Select Sheets("List").Range("e2") then select until the column is empty and use that as my SourceRange. VBA Code: -------------------- Sub copy_1() Dim SourceRange As Range Dim DestRange1 As Range Dim DestRange2 As Range Dim DestRange3 As Range Dim DestRange4 As Range Dim DestSheet1 As Worksheet, Lr As Long Dim DestSheet2 As Worksheet Dim DestSheet3 As Worksheet Dim DestSheet4 As Worksheet With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("Stock Summary").Range("A4:e55") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet1 = Sheets("Opening Stock") Lr = LastRow(DestSheet1) Set DestSheet2 = Sheets("Closing Stock") Lr = LastRow(DestSheet2) Set DestSheet3 = Sheets("Purchases") Lr = LastRow(DestSheet3) Set DestSheet4 = Sheets("Usage") Lr = LastRow(DestSheet4) 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange1 = DestSheet1.Range("A2") SourceRange.Copy DestRange1 Set DestRange2 = DestSheet2.Range("A2") SourceRange.Copy DestRange2 Set DestRange3 = DestSheet3.Range("A2") SourceRange.Copy DestRange3 Set DestRange4 = DestSheet4.Range("A2") SourceRange.Copy DestRange4 ' Set DestRange1 = DestSheet1.Range("A" & Lr + 1) ' SourceRange.Copy DestRange1 ' Set DestRange2 = DestSheet2.Range("A" & Lr + 1) ' SourceRange.Copy DestRange2 ' Set DestRange3 = DestSheet3.Range("A" & Lr + 1) ' SourceRange.Copy DestRange3 ' Set DestRange4 = DestSheet4.Range("A" & Lr + 1) ' SourceRange.Copy DestRange4 With Application .ScreenUpdating = True .EnableEvents = True End With End Sub -------------------- Thanks Albert -- Simon Lloyd Regards, Simon Lloyd 'Excel Chat' (http://www.thecodecage.com/forumz/chat.php) ------------------------------------------------------------------------ Simon Lloyd's Profile: 1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=186569 Excel Live Chat . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cell range to another sheet
Hi,
Brilliant. Works like a charm except has copied the formula. How do I get it just to paste the values? Thanks Albert "PY & Associates" wrote: On Mar 11, 4:21 pm, Albert wrote: Hi Guys, I am looking for some help. I have a macro that I have adapted from Ron de Bruin. Instead of setting the SourceRange explicitly I would like to code something like this: Select Sheets("List").Range("e2") then select until the column is empty and use that as my SourceRange. Sub copy_1() Dim SourceRange As Range Dim DestRange1 As Range Dim DestRange2 As Range Dim DestRange3 As Range Dim DestRange4 As Range Dim DestSheet1 As Worksheet, Lr As Long Dim DestSheet2 As Worksheet Dim DestSheet3 As Worksheet Dim DestSheet4 As Worksheet With Application .ScreenUpdating = False .EnableEvents = False End With 'fill in the Source Sheet and range Set SourceRange = Sheets("Stock Summary").Range("A4:e55") 'Fill in the destination sheet and call the LastRow 'function to find the last row Set DestSheet1 = Sheets("Opening Stock") Lr = LastRow(DestSheet1) Set DestSheet2 = Sheets("Closing Stock") Lr = LastRow(DestSheet2) Set DestSheet3 = Sheets("Purchases") Lr = LastRow(DestSheet3) Set DestSheet4 = Sheets("Usage") Lr = LastRow(DestSheet4) 'With the information from the LastRow function we can 'create a destination cell and copy/paste the source range Set DestRange1 = DestSheet1.Range("A2") SourceRange.Copy DestRange1 Set DestRange2 = DestSheet2.Range("A2") SourceRange.Copy DestRange2 Set DestRange3 = DestSheet3.Range("A2") SourceRange.Copy DestRange3 Set DestRange4 = DestSheet4.Range("A2") SourceRange.Copy DestRange4 ' Set DestRange1 = DestSheet1.Range("A" & Lr + 1) ' SourceRange.Copy DestRange1 ' Set DestRange2 = DestSheet2.Range("A" & Lr + 1) ' SourceRange.Copy DestRange2 ' Set DestRange3 = DestSheet3.Range("A" & Lr + 1) ' SourceRange.Copy DestRange3 ' Set DestRange4 = DestSheet4.Range("A" & Lr + 1) ' SourceRange.Copy DestRange4 With Application .ScreenUpdating = True .EnableEvents = True End With End Sub Thanks Albert Something like this? With Sheets("List") lr = .Range("e2").End(xlDown).Row Set sourcerng = .Range("e2:e" & lr) End With . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell range in other sheet | Excel Worksheet Functions | |||
Selecting Range Based on Cell Content then Copy/Paste to New Sheet,Looping | Excel Programming | |||
Finding a named range based on cell value and copy/paste to same sheet? | Excel Programming | |||
To copy from a range in another sheet to the active cell | Excel Programming |