Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
Any body help with a macro please........
I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
The macro recorder will give you the necessary code.
Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 16:32:01 -0700, VLOOKUP fORMULA wrote: Any body help with a macro please........ I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
Tks noted, I like the selection for copy to be selected by me, the marco only
selects the same cells while recording. kindly reply "Gord Dibben" wrote: The macro recorder will give you the necessary code. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 16:32:01 -0700, VLOOKUP fORMULA wrote: Any body help with a macro please........ I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
Sub test()
Sheets("Sheet2").Cells(1).EntireRow.Insert ActiveCell.Resize(1, 2).Copy Destination:=Sheets("Sheet2").Range("B3") End Sub Gord On Tue, 23 Mar 2010 10:53:01 -0700, VLOOKUP fORMULA wrote: Tks noted, I like the selection for copy to be selected by me, the marco only selects the same cells while recording. kindly reply "Gord Dibben" wrote: The macro recorder will give you the necessary code. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 16:32:01 -0700, VLOOKUP fORMULA wrote: Any body help with a macro please........ I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
Sub test()
ActiveCell.Resize(1, 1).Copy Destination:=Sheets("Sheet2").Range("B20") End Sub Thanks this works fine. Is it possible to modify a little bit. With the above code "the cell wherever i placed in Sheet1" is copying to B20 (Sheet2). When I run the Subtest() 2nd time, if b20 is not blank copy to B21 When I run the Subtest() 3rd time, if b21 is not blank copy to b22 When I run the subtest() 4th time, if b22 is not blank copy to b23..... I want to do it till B32 My purpose is :- I have Students name in Sheet1 and I have a transportation form in Sheet2 Name of student column (B20 to 32) 13 students can accomodate in one form/transportation, just to insert the name of the selected students in the form (B20:B32) without typing, Can you please suggest any code for that? "Gord Dibben" wrote: Sub test() Sheets("Sheet2").Cells(1).EntireRow.Insert ActiveCell.Resize(1, 2).Copy Destination:=Sheets("Sheet2").Range("B3") End Sub Gord On Tue, 23 Mar 2010 10:53:01 -0700, VLOOKUP fORMULA wrote: Tks noted, I like the selection for copy to be selected by me, the marco only selects the same cells while recording. kindly reply "Gord Dibben" wrote: The macro recorder will give you the necessary code. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 16:32:01 -0700, VLOOKUP fORMULA wrote: Any body help with a macro please........ I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance . . |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
Do you have Sheet2 filled from B1:B19 and looking for next blank cell below
that? Which would be B20 first time you run Sub test() Next time you run Sub test() next blank cell would be B21, etc. Sub test() Dim rng As Range Dim lRow As Long lRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Set rng = Sheets("Sheet2").Range("B" & lRow) ActiveCell.Resize(1, 1).Copy Destination:=rng End Sub Please note: ActiveCell.Resize(1, 1) is same as ActiveCell No resizing is done with (1, 1) Gord On Wed, 24 Mar 2010 16:53:01 -0700, VLOOKUP fORMULA wrote: Sub test() ActiveCell.Resize(1, 1).Copy Destination:=Sheets("Sheet2").Range("B20") End Sub Thanks this works fine. Is it possible to modify a little bit. With the above code "the cell wherever i placed in Sheet1" is copying to B20 (Sheet2). When I run the Subtest() 2nd time, if b20 is not blank copy to B21 When I run the Subtest() 3rd time, if b21 is not blank copy to b22 When I run the subtest() 4th time, if b22 is not blank copy to b23..... I want to do it till B32 My purpose is :- I have Students name in Sheet1 and I have a transportation form in Sheet2 Name of student column (B20 to 32) 13 students can accomodate in one form/transportation, just to insert the name of the selected students in the form (B20:B32) without typing, Can you please suggest any code for that? "Gord Dibben" wrote: Sub test() Sheets("Sheet2").Cells(1).EntireRow.Insert ActiveCell.Resize(1, 2).Copy Destination:=Sheets("Sheet2").Range("B3") End Sub Gord On Tue, 23 Mar 2010 10:53:01 -0700, VLOOKUP fORMULA wrote: Tks noted, I like the selection for copy to be selected by me, the marco only selects the same cells while recording. kindly reply "Gord Dibben" wrote: The macro recorder will give you the necessary code. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 16:32:01 -0700, VLOOKUP fORMULA wrote: Any body help with a macro please........ I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance . . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
Dear Gord,
It is working fine but only little problem exists, When I run the Sub test() first time. It starts from B2 instead of B20, remaining everything OK. How can I start this sequence from B20? Thanks Uday "Gord Dibben" wrote: Do you have Sheet2 filled from B1:B19 and looking for next blank cell below that? Which would be B20 first time you run Sub test() Next time you run Sub test() next blank cell would be B21, etc. Sub test() Dim rng As Range Dim lRow As Long lRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Set rng = Sheets("Sheet2").Range("B" & lRow) ActiveCell.Resize(1, 1).Copy Destination:=rng End Sub Please note: ActiveCell.Resize(1, 1) is same as ActiveCell No resizing is done with (1, 1) Gord On Wed, 24 Mar 2010 16:53:01 -0700, VLOOKUP fORMULA wrote: Sub test() ActiveCell.Resize(1, 1).Copy Destination:=Sheets("Sheet2").Range("B20") End Sub Thanks this works fine. Is it possible to modify a little bit. With the above code "the cell wherever i placed in Sheet1" is copying to B20 (Sheet2). When I run the Subtest() 2nd time, if b20 is not blank copy to B21 When I run the Subtest() 3rd time, if b21 is not blank copy to b22 When I run the subtest() 4th time, if b22 is not blank copy to b23..... I want to do it till B32 My purpose is :- I have Students name in Sheet1 and I have a transportation form in Sheet2 Name of student column (B20 to 32) 13 students can accomodate in one form/transportation, just to insert the name of the selected students in the form (B20:B32) without typing, Can you please suggest any code for that? "Gord Dibben" wrote: Sub test() Sheets("Sheet2").Cells(1).EntireRow.Insert ActiveCell.Resize(1, 2).Copy Destination:=Sheets("Sheet2").Range("B3") End Sub Gord On Tue, 23 Mar 2010 10:53:01 -0700, VLOOKUP fORMULA wrote: Tks noted, I like the selection for copy to be selected by me, the marco only selects the same cells while recording. kindly reply "Gord Dibben" wrote: The macro recorder will give you the necessary code. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 16:32:01 -0700, VLOOKUP fORMULA wrote: Any body help with a macro please........ I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance . . . |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
I assumed that B19 was filled.
I'm going to have to work on this to figure out how to start at B20 if no cells filled above and allow for the Offset(1, 0) Maybe someone else with more skills will jump in meantime. Gord On Thu, 25 Mar 2010 16:17:02 -0700, VLOOKUP fORMULA wrote: Dear Gord, It is working fine but only little problem exists, When I run the Sub test() first time. It starts from B2 instead of B20, remaining everything OK. How can I start this sequence from B20? Thanks Uday "Gord Dibben" wrote: Do you have Sheet2 filled from B1:B19 and looking for next blank cell below that? Which would be B20 first time you run Sub test() Next time you run Sub test() next blank cell would be B21, etc. Sub test() Dim rng As Range Dim lRow As Long lRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Set rng = Sheets("Sheet2").Range("B" & lRow) ActiveCell.Resize(1, 1).Copy Destination:=rng End Sub Please note: ActiveCell.Resize(1, 1) is same as ActiveCell No resizing is done with (1, 1) Gord On Wed, 24 Mar 2010 16:53:01 -0700, VLOOKUP fORMULA wrote: Sub test() ActiveCell.Resize(1, 1).Copy Destination:=Sheets("Sheet2").Range("B20") End Sub Thanks this works fine. Is it possible to modify a little bit. With the above code "the cell wherever i placed in Sheet1" is copying to B20 (Sheet2). When I run the Subtest() 2nd time, if b20 is not blank copy to B21 When I run the Subtest() 3rd time, if b21 is not blank copy to b22 When I run the subtest() 4th time, if b22 is not blank copy to b23..... I want to do it till B32 My purpose is :- I have Students name in Sheet1 and I have a transportation form in Sheet2 Name of student column (B20 to 32) 13 students can accomodate in one form/transportation, just to insert the name of the selected students in the form (B20:B32) without typing, Can you please suggest any code for that? "Gord Dibben" wrote: Sub test() Sheets("Sheet2").Cells(1).EntireRow.Insert ActiveCell.Resize(1, 2).Copy Destination:=Sheets("Sheet2").Range("B3") End Sub Gord On Tue, 23 Mar 2010 10:53:01 -0700, VLOOKUP fORMULA wrote: Tks noted, I like the selection for copy to be selected by me, the marco only selects the same cells while recording. kindly reply "Gord Dibben" wrote: The macro recorder will give you the necessary code. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 16:32:01 -0700, VLOOKUP fORMULA wrote: Any body help with a macro please........ I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance . . . |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Selection copy and insert to other sheet (Macro)
If you're still around<g
Sub test() Dim rng As Range Dim lRow As Long Dim startcell As Range Set startcell = Sheets("Sheet2").Range("B20") lRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Set rng = Sheets("Sheet2").Range("B" & lRow) If Sheets("Sheet2").Range("B20") = "" Then ActiveCell.Resize(1, 2).Copy Destination:=startcell Else ActiveCell.Resize(1, 2).Copy Destination:=rng End If End Sub Gord On Fri, 26 Mar 2010 13:24:10 -0700, Gord Dibben <gorddibbATshawDOTca wrote: I assumed that B19 was filled. I'm going to have to work on this to figure out how to start at B20 if no cells filled above and allow for the Offset(1, 0) Maybe someone else with more skills will jump in meantime. Gord On Thu, 25 Mar 2010 16:17:02 -0700, VLOOKUP fORMULA wrote: Dear Gord, It is working fine but only little problem exists, When I run the Sub test() first time. It starts from B2 instead of B20, remaining everything OK. How can I start this sequence from B20? Thanks Uday "Gord Dibben" wrote: Do you have Sheet2 filled from B1:B19 and looking for next blank cell below that? Which would be B20 first time you run Sub test() Next time you run Sub test() next blank cell would be B21, etc. Sub test() Dim rng As Range Dim lRow As Long lRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Set rng = Sheets("Sheet2").Range("B" & lRow) ActiveCell.Resize(1, 1).Copy Destination:=rng End Sub Please note: ActiveCell.Resize(1, 1) is same as ActiveCell No resizing is done with (1, 1) Gord On Wed, 24 Mar 2010 16:53:01 -0700, VLOOKUP fORMULA wrote: Sub test() ActiveCell.Resize(1, 1).Copy Destination:=Sheets("Sheet2").Range("B20") End Sub Thanks this works fine. Is it possible to modify a little bit. With the above code "the cell wherever i placed in Sheet1" is copying to B20 (Sheet2). When I run the Subtest() 2nd time, if b20 is not blank copy to B21 When I run the Subtest() 3rd time, if b21 is not blank copy to b22 When I run the subtest() 4th time, if b22 is not blank copy to b23..... I want to do it till B32 My purpose is :- I have Students name in Sheet1 and I have a transportation form in Sheet2 Name of student column (B20 to 32) 13 students can accomodate in one form/transportation, just to insert the name of the selected students in the form (B20:B32) without typing, Can you please suggest any code for that? "Gord Dibben" wrote: Sub test() Sheets("Sheet2").Cells(1).EntireRow.Insert ActiveCell.Resize(1, 2).Copy Destination:=Sheets("Sheet2").Range("B3") End Sub Gord On Tue, 23 Mar 2010 10:53:01 -0700, VLOOKUP fORMULA wrote: Tks noted, I like the selection for copy to be selected by me, the marco only selects the same cells while recording. kindly reply "Gord Dibben" wrote: The macro recorder will give you the necessary code. Gord Dibben MS Excel MVP On Mon, 22 Mar 2010 16:32:01 -0700, VLOOKUP fORMULA wrote: Any body help with a macro please........ I have selected two cells from a row from sheet1 Is there any way to copy those cells go to Sheet2 and insert a blank row in A1 and paste those infor on B3 tks in advance . . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selection copy and insert to other sheet | Excel Discussion (Misc queries) | |||
Help creating a macro to copy and paste a certain selection | Excel Discussion (Misc queries) | |||
Help creating a macro to copy and paste a certain selection | Excel Discussion (Misc queries) | |||
Help creating a macro to copy and paste a certain selection | Excel Discussion (Misc queries) | |||
Macro: Insert, copy and past data from sheet | Excel Discussion (Misc queries) |