Macro to copy cells from one worksheet to another 1

[anet]

I am trying to write a macro to achieve the following:
-the user highlights the row in the worksheet that they wish to use and clicks the command button to start the macro
-the macro copies specific cells from the highlighted row and then pastes just these cells to another worksheet in the same workbook. The destination worksheet is not the same format as the worksheet I am copying from, so each cell will have to be pasted individually.

Here is what I have so far:

If Not IsEmpty(Selection.Value) Then
'produce a message box just to see if macro is running
MsgBox "Macro is running."
'put code here to copy the row that the user highlights
'also code to paste only specific cells to another worksheet
Else
'if the user has not highlighted a row
MsgBox "Please select a week to process."
End If

What is happening now is that the "Macro is running." message box pops up when I click the command button, even though the user has not selected any text yet.

How do I change my code to achieve the results I want?
Thanks.

 

[VulcanJedi]

you can't not use the msgbox function, its modal and requires input before execution resumes. Please search the forums for how to make a 'macro running' window.
is this fundamentally what you want?
Sub sss()

myrow = InputBox("Which row?&quot

worksheet(your sheet).cells(1,2) = worksheet(old sheet).cells(myrow,2)




End Sub
Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[anet]

The first message box was just for me for testing purposes. I have removed it. I guess what I am really asking is how I reference the row that has been selected by the user, but then only copying specific cells from that row to another worksheet.

I don't really understand the snippet of code that you posted. Sorry. This is the first time I have tried writing an Excel macro.

Thanks.

 

[RobBroekhuis]

I think your problem is with the IsEmpty condition. What exactly are you checking for, i.e., what would constitute a valid selection? Note that there generally is at least one cell "selected" on the worksheet.
Rob

 

[anet]

That makes sense! So, how do I make sure that the user has highlighted a row (and only one row) and then copy some of the cells from that row onto another worksheet?

Thanks.

 

[RobBroekhuis]

if selection.rows.count=1 and selection.columns.count=256 then
...
end if

This works if the ENTIRE row is selected - is that what you're looking for?
Rob

 

[anet]

Thanks Rob. Now I have another problem. Here is my code:

Private Sub cmdPaySlip_Click()
Dim myRow as String (????)'is this the right datatype?

If Selection.Rows.Count = 1 And Selection.Columns.Count = 256 Then
myRow = Selection.?????
Range(myRow,4).Select
Selection.Copy
Sheets("Pay Slip&quot.Select
Range("B6&quot.Select
ActiveSheet.Paste
Else
MsgBox ("Please select a week to process.&quot
End If
End Sub

As you can see, I am not sure how to assign the selected row to the variable. Also, when it gets to the line Range("B6&quot.Select it craps out and I get "Run-time error 1004. Method range of object worksheet failed." However, if I hard code in a range to copy and then comment out the line selecting a different worksheet, it will paste the selection back to the same worksheet fine. Why doesn't it work when I try to copy it to the payslip worksheet? I did double check to make sure I have the worksheet name right. I also tried recording a macro just to copy one cell from one worksheet to another using the Excel menu and it also works fine. I tried copying the generated code from this macro into my macro, but I get the same error message.

Thanks for you help.