Want macro to select & copy cells from a different worksheet

G

Guest

I have a spreadsheet with "Sheet 1" and "Sheet 2"
Both have some data
I want a command button on Sheet 1 with code that will copy & move some data
on Sheet 1 elsewhere on Sheet 1, then on Sheet 2 copy & move some other data
on Sheet 2 elsewhere on Sheet 2 then return active cell to Sheet 1
If I record a macro and run it it works perfectly
If I copy that code into the Command Button code it has an error when it
selects the data on Sheet 2
 
G

Guest

I should have - sorry
This stops with the Range("B4").Select line highlighted

Private Sub CommandButton1_Click()
'
' Macro1 Macro
' Macro recorded 27/02/2006 by Hughes
'

'

Sheets("Sheet2").Select
Range("B4").Select
Rows("1:3").Select
Selection.Copy
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Range("B4").Select
Rows("1:3").Select
Application.CutCopyMode = False
Selection.Copy
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("B5").Select
Application.CutCopyMode = False

End Sub

It stops in debug with the
 
D

Don Guillett

try this idea. Modify to suit and repeat for the second sheet. The first
line are the DESTINATION rows and the second is the SOURCE rows. Notice that
they are the SAME SIZE. This can be done from anywhere in the workbook with
NO selections necessary or desirable.

Sub copyrowvalues()
Sheets("sheet1").Rows("18:20").Value = _
Sheets("sheet1").Rows("8:10").Value

Sheets("sheet2").Rows("18:20").Value = _
Sheets("sheet2").Rows("8:10").Value
End Sub
 
G

Guest

Oh, well done!!
Thank you very much!

Don Guillett said:
try this idea. Modify to suit and repeat for the second sheet. The first
line are the DESTINATION rows and the second is the SOURCE rows. Notice that
they are the SAME SIZE. This can be done from anywhere in the workbook with
NO selections necessary or desirable.

Sub copyrowvalues()
Sheets("sheet1").Rows("18:20").Value = _
Sheets("sheet1").Rows("8:10").Value

Sheets("sheet2").Rows("18:20").Value = _
Sheets("sheet2").Rows("8:10").Value
End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to insert new worksheet based on # of rows in a data sourch 2
Macro to copy value in field and insert it into filter 2
Copying parts of a sheet with macro 7
Excel VBA to move cells to sheet2 based on selection in sheet1 0
problems getting this macro to work 23
Copy Sheet to new Sheet and clear cells on original sheets 7
Copying data to the next blank row 0
problem with fractions to decimal 1

Top