how do you get a excel macro to copy parts of a list into another list?

Question

i have a worksheet called 'place order' which has numbers in colum B, text in colum C, and prices in columD between rows 3-49
i need a macro so that when a button is clicked, if the number in columB is more than 0, BCD are copied onto another worksheet named 'Receipt' for that row. i also need the copied rows to form a list.
i have no idea where to even start with creating this macro, ive never made a macro like this before

help would be greatly appreciated

thanks

- if you need any more information please let me know

Answer 1

Here is a simple macro that does explicitly what you wish. However, you don't state whether you wish to clear the 'Receipt' sheet first when you run the macro or just keep appending data to the 'Receipt' sheet.



The following example will append data each time you call the macro.



Copy the following macro to the clipboard:



Sub CopyData()

Dim i, LastRow

LastRow = Range("B" & Rows.Count).End(xlUp).Row

For i = 3 To 49

If Cells(i, "B").Value > 0 Then

Range("B" & i & ":" & "D" & i).Copy Destination:= _

Sheets("Receipt").Range("B" & Rows.Count).End(xlUp).Offset(1)

End If

Next

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



From here, you can create a command button or a keyboard shortcut to call the macro.



========



Command Button for Excel 2003:



If the Forms toolbar is not visible, go to View > Toolbars and click 'Forms'.



Once visible, click the command button icon on the Forms toolbar and then click in the body of the worksheet and drag a button onto the worksheet. Size and position it to suit.



The Assign Macro window will open. Select the macro above and click 'OK'.



You can right click the command button and Edit Text to change the caption.



========



Command Button for Excel 2010:



If the Developer tab is not visible, go to File > Options and select 'Customize Ribbon'.



Check the 'Developer' check box on the right side of the window under 'Customize the Ribbon'.



Click 'OK'.



Select the Developer tab and from the Insert dropdown, click the 'button' icon under Form Controls.



Click in the worksheet and drag a command button onto the sheet. Assign the macro above when the Assign Macro window opens and click 'OK'.



========



Keyboard Shortcut:



From the worksheet, after creating the macro above, press ALT + F8



When the Macros window opens, highlight the macro if it is not already highlighted and click 'Options...'.



Enter a letter to be used as a keyboard shortcut and click 'OK'.



Close the Macros window.



To run the macro at any time, press CTRL + your shortcut letter.

Answer 2

First you can record the action into a macro and then update it to be more user friendly, or you can write one from scratch.

Something like this should give you a place to start:

Public Sub MakeReceipt()

Dim sOrder As String

Dim sReceipt As String

Dim dLoop As Double

Dim dRow As Double



On Error GoTo mrError



sOrder = "Place Order"

sReceipt = "Receipt"



With ThisWorkbook.Sheets(sOrder)

.Activate

For dLoop = 3 To 49

If .Cells(dLoop, 2).Value > 0 Then

dRow = dLoop

ThisWorkbook.Sheets(sReceipt).Cells(dRow, 2).Value = .Cells(dLoop, 2).Value

ThisWorkbook.Sheets(sReceipt).Cells(dRow, 3).Value = .Cells(dLoop, 3).Value

ThisWorkbook.Sheets(sReceipt).Cells(dRow, 4).Value = .Cells(dLoop, 4).Value

End If



Next dLoop



End With

Exit Sub

mrError:

MsgBox "Receipt Error: " & Err.Number & vbCrLf & Err.Description, vbOKOnly + vbExclamation, "Create Receipt"

End Sub

Answer 3

i might think of interior the 2nd macro that the cut back action is complicated because of the fact the rows series merchandise interior the 2nd macro belongs to the lively sheet, yet is then calls an merchandise that may no longer lie on an identical sheet: Rows(rng(a million).Row).cut back ? i might basically use the selection.advancedfilter approach to do the reproduction, and then delete the suited rows.