anyone good with macro's? could use some help

[discodave]

hi im creating a booking system for a takeaway

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

 

[rs2k]

Hi Dave,

This should get you started; try it in a copy of your workbook first and adjust ranges to suit, if needed.

Sub Find()
Dim DestSheet As Worksheet
Dim ws As Worksheet
Set DestSheet = Worksheets("Sheet2")
  
  Dim sRow       As Long     'row index on source worksheet
  Dim dRow       As Long     'row index on destination worksheet
  
  dRow = 2
  
  For sRow = 1 To Range("B65536").End(xlUp).Row
     'use pattern matching to find client anywhere in cell
     If Cells(sRow, "B") > 0 Then
        dRow = dRow + 1
        Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "A")
        Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "B")
        Cells(sRow, "D").Copy Destination:=DestSheet.Cells(dRow, "C")
     End If
  Next sRow
End Sub

HTH
Colin

 

[discodave]

Hi Dave,

This should get you started; try it in a copy of your workbook first and adjust ranges to suit, if needed.

Sub Find()
Dim DestSheet As Worksheet
Dim ws As Worksheet
Set DestSheet = Worksheets("Sheet2")
  
  Dim sRow       As Long     'row index on source worksheet
  Dim dRow       As Long     'row index on destination worksheet
  
  dRow = 2
  
  For sRow = 1 To Range("B65536").End(xlUp).Row
     'use pattern matching to find client anywhere in cell
     If Cells(sRow, "B") > 0 Then
        dRow = dRow + 1
        Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "A")
        Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "B")
        Cells(sRow, "D").Copy Destination:=DestSheet.Cells(dRow, "C")
     End If
  Next sRow
End Sub

HTH
Colin

thank you so much, you are an absolute life saver. it works perfectly

 

[bertie]

Re: macro help

I have created two variables for your worksheets.

   [COLOR=darkblue]Set[/COLOR] wsPo = Worksheets("Place Order")
   [COLOR=darkblue]Set[/COLOR] wsRcpt = Worksheets("Receipt")

I then loop through column B of the Place Order sheet checking to see if the value is greater than zero.

   [COLOR=darkblue]For[/COLOR] i = 3 [COLOR=darkblue]To[/COLOR] 49
      [COLOR=darkblue]If[/COLOR] wsPo.Range("B" & i).Value > 0 [COLOR=darkblue]Then[/COLOR]

If this returns True then the values are transferred to the Receipt worksheet.

         rw = rw + 1
         wsRcpt.Range("A" & rw).Value = wsPo.Range("B" & i).Value
         wsRcpt.Range("B" & rw).Value = wsPo.Range("C" & i).Value
         wsRcpt.Range("C" & rw).Value = wsPo.Range("D" & i).Value

The full code is shown below.
To use:
Press Alt + F11 to open the VBA editor.
Click Insert => Module.
Copy and paste the code below.
Press F5 to run the code.

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Sub[/COLOR] ReceiptOutput()
   [COLOR=darkblue]Dim[/COLOR] wsPo [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] wsRcpt [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] rw [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]    [COLOR=green]'output row on Receipt sheet[/COLOR]
 
   [COLOR=darkblue]Set[/COLOR] wsPo = Worksheets("Place Order")
   [COLOR=darkblue]Set[/COLOR] wsRcpt = Worksheets("Receipt")
   rw = 1
 
   [COLOR=green]'loop through the numbers on the Place Order sheet[/COLOR]
   [COLOR=darkblue]For[/COLOR] i = 3 [COLOR=darkblue]To[/COLOR] 49
      [COLOR=darkblue]If[/COLOR] wsPo.Range("B" & i).Value > 0 [COLOR=darkblue]Then[/COLOR]
         [COLOR=green]'increment the output row[/COLOR]
         rw = rw + 1
         wsRcpt.Range("A" & rw).Value = wsPo.Range("B" & i).Value
         wsRcpt.Range("B" & rw).Value = wsPo.Range("C" & i).Value
         wsRcpt.Range("C" & rw).Value = wsPo.Range("D" & i).Value
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Next[/COLOR] i
 
   [COLOR=green]'tidy up[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsPo = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsRcpt = [COLOR=darkblue]Nothing[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

To add a command button to the Place Order sheet:
Right click any toolbar.
Select Control Toolbox.
Select the command button icon and place it anywhere on your sheet.
Double click the command button.
Insert this code:

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click()
   ReceiptOutput
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Enable the command button by toggling the design mode icon, top left on the control toolbox.