Previous Topic Next Topic

Auto fill a Macro

Closed
marco 1 - Apr 14, 2016 at 12:40 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Apr 20, 2016 at 07:55 AM
Hello,
I want to auto fill a macro button down column A, that copies the information from several cells to a different sheet in the work book. here is the code I have, just not sure how to go about auto filling it down.. :

Sub Sale()
'
' Sale Macro
'
'
Range("B2").Select
Selection.Copy
Sheets("Payment Sheet").Select
Range("F2").Select
ActiveSheet.Paste
Sheets("April").Select
Range("C2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payment Sheet").Select
Range("D3:F3").Select
ActiveSheet.Paste
Sheets("April").Select
Range("D2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payment Sheet").Select
Range("B3").Select
ActiveSheet.Paste
Sheets("April").Select
Range("E2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payment Sheet").Select
Range("B4").Select
ActiveSheet.Paste
Sheets("April").Select
Range("F2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payment Sheet").Select
Range("B3").Select
Range("D5:F5").Select
ActiveSheet.Paste
Range("B6").Select

End Sub






Thanks,

John
Related:

3 responses

Answer 1 / 3
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Apr 16, 2016 at 04:14 AM
Hello John,

I'm not sure what you mean by "fill a macro button down Column A".

From what I can tell, your code appears to be transferring various cells of data from a monthly sheet to a "Payment" sheet. Could you elaborate further and supply a sample of your work book so that we can see what it is exactly that you are trying to do.

Upload a sample of your work book to a free file sharing site such as DropBox, ge.tt or SpeedyShare and then post the link to your file back here. Be careful with any sensitive data (use dummy data).

Cheerio,
vcoolio.
0
John
Apr 18, 2016 at 02:06 PM
I have rows with Information, like Customers name, phone number, Email, Ect. And i want column A to have a button that says "Sale" and if a sale is made I want to be able to click on the Sale button. That button needs to copy things like Name, phone number, email, to Payment sheet from that row. I have over 900 rows, each row is a different customer. So i want something like Auto fill to copy the macro down to each row with that button..
0
Answer 2 / 3
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Apr 19, 2016 at 07:41 AM
Hello John,

This can be done without any buttons as the following code will show you:- 


Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

          Dim lCol As Long
lCol = Cells(1, Columns.Count).End(xlToLeft).Column

If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once
If Not Application.Intersect(Target, Range("A:A")) Is Nothing Then

        If Target = "Sale" Then
        Range(Cells(Target.Row, "A"), Cells(Target.Row, lCol)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        Target.EntireRow.Delete
        End If
  End If
  
Sheet2.Columns.AutoFit
Sheet2.Select

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code is a Worksheet_Change event and will transfer a row of data each time that "Sale" is typed into a cell in Column A.

Following is the link to my test work book which will show you how it works:-

https://www.dropbox.com/s/2fduo5g8tpzw9if/John%28Worksheet_Change%20event%29.xlsm?dl=0

Type the word "Sale" into any cell in Column A then click away (or press enter or down arrow) and the relevant row of data will be transferred to the next available row in sheet2.

The code will also delete the "used" data from the source sheet once transferred to the Payment sheet.
The code will take you directly to Sheet2 (or Payment sheet) after each transfer. This could become quite annoying so, if it does, then remove line 20 from the code.

The code needs to go into the work sheet module so, to implement the code into your work book, right click on the source sheet tab and select "view code" from the menu that appears. In the big white field that appears, paste the above code. Go back to your work sheet and test it.

Test the code in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.
0
John
Apr 19, 2016 at 10:40 AM
Hi Vcoolio,

Thats great, but not exactly what i am looking for. I want something like this: Name on sheet 1 to go into name field on payment sheet, Email on sheet 1 to go into payment sheet where email is. The cells are not in a row. Customers name on Payment sheet is B2, and Email is D5, Company name is D3, ECT. I have attached a dummy copy of what i am talking about. It shows a January tab that the customers info is coming from, and a payment sheet tab. As each Lead is not going to be a sale I would prefer to have a sale button in column A. that a sales rep can click on to make it into a Sale. Hope that helps. And thanks for all your time on this!

https://www.dropbox.com/s/kv46ig4nt99nun2/Leads%201.2.xlsm?dl=0
0
Answer 3 / 3
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
Apr 20, 2016 at 07:55 AM
Hello John,

Based on your opening post and your post #2 then, you just want to place a button in each cell in Column A for 900 odd rows of data. This is not a good idea as it is a massive drain on resource. However, the following code would do as you ask:- 

Sub CreateButtons()

Application.ScreenUpdating = False
 
        Dim But As Button
        Dim r As Range
        Dim i As Integer
        
ActiveSheet.Buttons.Delete
  
For i = 2 To 10   ----> 'Change the 10 to 900 or however many rows                   
        Set r = ActiveSheet.Range(Cells(i, 1), Cells(i, 1))
        Set But = ActiveSheet.Buttons.Add(r.Left, r.Top, r.Width, r.Height)
With But
      .OnAction = "Sale"  'calls your Sale macro
      .Caption = "Sale"
      .Name = "Sale"
    End With
Next i

  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  
End Sub


In your "Sale" macro code, change April to January to test the code above as this is the month name in your sample.

BTW, your code is "hard coded" which means that the same data will be copied/pasted each time that any button is pressed. You may want to address this.

I hope that this helps.

Cheerio,
vcoolio.
0