anyone good with macro's? could use some help

D

discodave

New Member
Joined
Mar 21, 2011
Messages
9
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
Hi Dave,

This should get you started; try it in a copy of your workbook first and adjust ranges to suit, if needed.
Code: 
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
 
Upvote 0
rs2k said:
Hi Dave,

This should get you started; try it in a copy of your workbook first and adjust ranges to suit, if needed.
Code: 
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
Click to expand...
thank you so much, you are an absolute life saver. it works perfectly
 
Upvote 0
Re: macro help

I have created two variables for your worksheets.
Code: 
   [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.
Code: 
   [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.
Code: 
         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.

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:
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Cannot assign macro to button
2
Replies
10
Views
239
SPS41
S
A
  • Question Question
Macro copy text above
Replies
1
Views
144
Kaper
Kaper
D
  • Question Question
Macro Insert column with date of today
Replies
1
Views
68
MikeVol
MikeVol
R
  • Solved
run macros from a list of names in a work sheet one at a time by using the same pressing shift and letter
Replies
5
Views
86
Peter_SSs
Peter_SSs
Sean15
  • Question Question
Could you help improve recorded macro
2
Replies
13
Views
461
Sean15
Sean15

Forum statistics

Threads
1,224,521
Messages
6,179,289
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top