Automatically pull data from one file to populate a template

[jmootrey]

Hi Everyone...
Just beginning to get the hang of vba coding in excel. I would lik
to implement a new feature in a spreadsheet here at the office. W
currently have a master records sheet containing vital information o
unit processing. Each unit then has a sub file containing additiona
information. Currently one must populate the master record file. The
open a template and populate another file with the same information.
would like to streamline this process and have the data in the maste
record file automatically inserted into the template, allowing the use
to only have to fill in a few other minor details. What I would like i
to have a "Create Record" button. When clicking the button the use
should be prompted for either a record number (stored in the B column
or a row number (not as elegant). The macro would then pole that row (x
and transfer the value of cells BX, CX, DX, etc to cell A3, D9, E6 et
in the newly created file based on an existing template (template.xlt
Any help would be greatly appreciated as I am lost when it comes t
something this complex

 

[Bernie Deitrick]

I have assumed that your data file is named

Master Record.xls

and that the data is on a sheet named

Data Sheet

and that your template has a sheet named

Report Sheet




In your template's VBA project, insert a userform with a listbox and a commandbutton - named
Userform1, Listbox1, and Commandbutton1.

Then, use code like this (stored in a regular codemodule) to allow the user to select the record
number:

Sub SelectRecordNumber()
With Workbooks("Master Record.xls").Worksheets("Data Sheet")
UserForm1.ListBox1.List = .Range(.Range("B2"), .Cells(Rows.Count, 2).End(xlUp)).Value
End With
Load UserForm1
UserForm1.Show
End Sub


Double click the commandbutton, and use this code for the click event

Private Sub CommandButton1_Click()
Dim i As Integer
Dim Sel As Boolean
Dim myRow As Double
Dim myR As String

Sel = False

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
MsgBox "You selected " & ListBox1.List(i)
myR = ListBox1.List(i)
Sel = True
Exit For
End If
Next i

If Sel Then
With Workbooks("Master Record.xls").Worksheets("Data Sheet")
myRow = Application.Match(myR, .Range("B:B"), False)
'Bring over the data
ThisWorkbook.Worksheets("Report Sheet").Range("A3").Value = .Cells(myRow, 2).Value 'from
column B
ThisWorkbook.Worksheets("Report Sheet").Range("D9").Value = .Cells(myRow, 3).Value 'from
column C
ThisWorkbook.Worksheets("Report Sheet").Range("E6").Value = .Cells(myRow, 4).Value 'from
column D
End With
Unload UserForm1
Exit Sub
End If
MsgBox "Select something!"
End Sub

I hope you can see the pattern for the data transfer.....

HTH,
Bernie
MS Excel MVP

Hi Everyone...
Just beginning to get the hang of vba coding in excel. I would like
to implement a new feature in a spreadsheet here at the office. We
currently have a master records sheet containing vital information on
unit processing. Each unit then has a sub file containing additional
information. Currently one must populate the master record file. Then
open a template and populate another file with the same information. I
would like to streamline this process and have the data in the master
record file automatically inserted into the template, allowing the user
to only have to fill in a few other minor details. What I would like is
to have a "Create Record" button. When clicking the button the user
should be prompted for either a record number (stored in the B column)
or a row number (not as elegant). The macro would then pole that row (x)
and transfer the value of cells BX, CX, DX, etc to cell A3, D9, E6 etc
in the newly created file based on an existing template (template.xlt)
Any help would be greatly appreciated as I am lost when it comes to
something this complex.


--
jmootrey
------------------------------------------------------------------------
jmootrey's Profile: 1387
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=170486

Microsoft Office Help