A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Selection copy and insert to other sheet (Macro)



 
 
Thread Tools Display Modes
  #1  
Old March 22nd, 2010, 11:32 PM posted to microsoft.public.excel.misc
VLOOKUP fORMULA
external usenet poster
 
Posts: 28
Default 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  
Old March 23rd, 2010, 02:45 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old March 23rd, 2010, 05:53 PM posted to microsoft.public.excel.misc
VLOOKUP fORMULA
external usenet poster
 
Posts: 28
Default 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  
Old March 23rd, 2010, 07:31 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old March 24th, 2010, 11:53 PM posted to microsoft.public.excel.misc
VLOOKUP fORMULA
external usenet poster
 
Posts: 28
Default 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  
Old March 25th, 2010, 03:37 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old March 25th, 2010, 11:17 PM posted to microsoft.public.excel.misc
VLOOKUP fORMULA
external usenet poster
 
Posts: 28
Default 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  
Old March 26th, 2010, 08:24 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default 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  
Old March 27th, 2010, 02:48 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Selection copy and insert to other sheet (Macro)

If you're still aroundg

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

.


.


.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:13 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.