Auto fill a Macro
Closed
marco 1
-
Apr 14, 2016 at 12:40 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 20, 2016 at 07:55 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Apr 20, 2016 at 07:55 AM
Related:
- Auto fill a Macro
- Grand theft auto 1 download - Download - Action and adventure
- Grand theft auto v free download no verification for pc - Download - Action and adventure
- Facebook auto refresh - Guide
- Nvidia drivers auto detect - Guide
- Auto redial - Guide
3 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Apr 16, 2016 at 04:14 AM
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.
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.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Apr 19, 2016 at 07:41 AM
Apr 19, 2016 at 07:41 AM
Hello John,
This can be done without any buttons as the following code will show you:-
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.
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.
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
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
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Apr 20, 2016 at 07:55 AM
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:-
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.
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.
Apr 18, 2016 at 02:06 PM