3kidsgrayhair
New Member
- Joined
- Jan 26, 2005
- Messages
- 3
Hello, I am a new member getting my feet wet with Macros. I have used the board to find some help but am having trouble with this problem.
1) Problem. I have two workbooks, one with customer info ("Customers") and another we are using to create and track projects ("T&M Project Sheet2"). In the T&M workbook I have included a drop down list from the forms toolbar listbox that lists Customers from the Customers workbook. When the user selects the Customer in the dropdown, I would like to draw more information about the customer (address, phone, etc) to populate specific cells on the T&M workbook.
2) Efforts. I can get the list to output the row number of the selected customer to a cell. I used the Macro recorder to copy and paste data, but it uses the specific row number I recorded, not the variable based on the customer selection. An abbreviated version of the code is below:
Sub customerfill()
'
' customerfill Macro
' copy and paste customer info
'
'
Application.ScreenUpdating = False
Windows("customers.xls").Activate
ActiveWindow.SmallScroll Down:=48
Windows("T&M Project Sheet2.xls").Activate
Windows("customers.xls").Activate
Range("D100").Select
Selection.Copy
Windows("T&M Project Sheet2.xls").Activate
Range("O13").Select
ActiveSheet.Paste
Windows("customers.xls").Activate
Range("B100").Select
Application.CutCopyMode = False
Selection.Copy
Windows("T&M Project Sheet2.xls").Activate
Range("O14").Select
ActiveSheet.Paste
End Sub
3) Questions. Can I replace the recorded row (100) with a variable based on the drop down list (i.e. 245)? If so, how do I do that? Is there a better way to do this?
Thanks so much for your help.
1) Problem. I have two workbooks, one with customer info ("Customers") and another we are using to create and track projects ("T&M Project Sheet2"). In the T&M workbook I have included a drop down list from the forms toolbar listbox that lists Customers from the Customers workbook. When the user selects the Customer in the dropdown, I would like to draw more information about the customer (address, phone, etc) to populate specific cells on the T&M workbook.
2) Efforts. I can get the list to output the row number of the selected customer to a cell. I used the Macro recorder to copy and paste data, but it uses the specific row number I recorded, not the variable based on the customer selection. An abbreviated version of the code is below:
Sub customerfill()
'
' customerfill Macro
' copy and paste customer info
'
'
Application.ScreenUpdating = False
Windows("customers.xls").Activate
ActiveWindow.SmallScroll Down:=48
Windows("T&M Project Sheet2.xls").Activate
Windows("customers.xls").Activate
Range("D100").Select
Selection.Copy
Windows("T&M Project Sheet2.xls").Activate
Range("O13").Select
ActiveSheet.Paste
Windows("customers.xls").Activate
Range("B100").Select
Application.CutCopyMode = False
Selection.Copy
Windows("T&M Project Sheet2.xls").Activate
Range("O14").Select
ActiveSheet.Paste
End Sub
3) Questions. Can I replace the recorded row (100) with a variable based on the drop down list (i.e. 245)? If so, how do I do that? Is there a better way to do this?
Thanks so much for your help.