macro to copy cells from random row to another worksheet

[nono]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/Bert/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; mso-bidi-font-size:12.0pt; font-family:Arial; mso-fareast-font-family:"Times New Roman";} pre {margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Courier New"; mso-fareast-font-family:"Courier New";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Hello,
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
What I would like to do with this macro:
- selection of a random row by myself on worksheet A
- selection / copy of different cells from that row
- selection of a random row by myself on worksheet B
- pasting contents of selected cells from A to the same cells on B
- again back to A, select row, select cells, ….., etc.
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Thanks a lot

 

[machopicho]

Try:

Sub Random()
r = Int(Rows.Count * Rnd()) + 1
rw = Int(Rows.Count * Rnd()) + 1
Sheets(1).Rows(r).Copy Destination:=Sheets(2).Rows(rw)
End Sub

If you want to border your number rows, substitute Rows.Count with a number delimiting your area.

 

[nono]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/Bert/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; mso-bidi-font-size:12.0pt; font-family:Arial; mso-fareast-font-family:"Times New Roman";} pre {margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Courier New"; mso-fareast-font-family:"Courier New";} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> When I copy the content of this code into a new macro nothing happens.
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
What I need is that the macro asks me which row I have to copy (with an InputBox ?). Then the selected cells of that row will be copied?
<!--[if !supportEmptyParas]--> <!--[endif]--><o></o>
Thanks

 

[machopicho]

You have wrong to write!!!!!!!!
Random is a function of excel with creates random numbers.
What do you want is:

Sub CopyRows()
r = Inputbox ("Which rows do you want to copy?")
rw = Inputbox ("In which rows do you want to paste?")
Sheets(1).Rows(r).Copy Destination:=Sheets(2).Rows(rw)

 

[nono]

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 9">****** name="Originator" content="Microsoft Word 9"><link rel="File-List" href="file:///C:/DOCUME%7E1/Bert/LOCALS%7E1/Temp/msoclip1/01/clip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:DoNotOptimizeForBrowser/> </w:WordDocument> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; mso-bidi-font-size:12.0pt; font-family:Arial; mso-fareast-font-family:"Times New Roman";} pre {margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Courier New"; mso-fareast-font-family:"Courier New";} span.highlight {mso-style-name:highlight;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 90.0pt 72.0pt 90.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> Sorry for writing the term “random” but I also wrote “selection of a random row by myself”.
The “Sub CopyRows()” macro copies the entire row instead of a selection of that row.
Sorry for misunderstanding.

 

[machopicho]

Sub selezione()
Dim rng, rnge As Range
Set rng = Application.InputBox(Prompt:="Selezione intervallo:", _
Title:="Intervallo da copiare", Type:=8)
Set rnge = Application.InputBox(Prompt:="Selezione intervallo:", _
Title:="Intervallo in cui copiare", Type:=8)
rng.Select
Selection.Copy
rnge.Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

 

[nono]

Thanks again for your effort.
When I run the macro then I have to select a range of cells.
This means the cells are connected with each other, for instance $A$29:$F$29.
However I would like to select individual cells, for instance $A$$29, $C$29, $E$29
Is this possible?

 

[machopicho]

You have to do only click on one cell.

 

[nono]

Thanks but it is not that easy.
Select the range to copy: $A$4,$C$4,$E$4 (okay)

Select the range to paste: $A$1
Result: cells are connected: $A$4,$B$4,$C$4
cell contents are not pasted in the original positions.

Select the range to paste: $A$1:$E$1
Result: Run-time error ‘1004’
The information cannot be paste because the Copy area and the paste area are not
the same size and shape.

When it is okay then by selecting for example a paste range: $B$2
then the result should be: $B$2,$D$2,$F$2

Otherwise you have to choose the exact format of the paste area somewhere in the worksheet.

What is your opinion?

Thanks

 

[machopicho]

I don't know what tell you.
you can try to add this cose's line at the beginning of teh macro:

On Error Resume Next

and staying to see what happens:

Sub selezione()
Dim rng, rnge As Range
[COLOR=red]On Error Resume Next[/COLOR]
Set rng = Application.InputBox(Prompt:="Selezione intervallo:", _
Title:="Intervallo da copiare", Type:=8)
Set rnge = Application.InputBox(Prompt:="Selezione intervallo:", _
Title:="Intervallo in cui copiare", Type:=8)
rng.Select
Selection.Copy
rnge.Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub