CODEWITHSUNDEEP
vbaexcelautomation
Anthony Loder
Anthony Loder

Reputation: 11

Populating multiple sheets with information from a table

I am working on filling out 100 instances of a form from data that I have in a master table. I am looking for a way to automate this.

The form has the fields NAME and DATE (as well as others) I am looking to create a code that will take the NAME and DATE from a row in the master table, fill it into the form that is on a separate sheet, and then repeat the process for the next row and sheet. Example:

Sheet 1: Will take NAME and DATE from Row A
Sheet 2: Will take NAME and DATE from Row B
Sheet 3: Will take NAME and DATE from Row C
...

I'm attempting to learn VB on the fly with this, but have been unsuccessful thus far.

Upvotes: 1

Views: 51

Answers (2)

David G
David G

Reputation: 2357

For my quick attempt to work, your sheets will need to have similar names with an incremented number at the end (sheet1, sheet2, ...) and your master data table sheet to be named "MasterData". Also it assumes your names are in column A and dates, column B, and that you start at the first row.

Sub NameDate()
    Dim i As Integer
    For i = 1 To 100 'assuming 100 sheets
        Sheets("Sheet" & i).Range("A1") = Sheets("MasterData").Range("A" & i) 'placing the name in row i in the Sheet's A1 cell
        Sheets("Sheet" & i).Range("B1") = Sheets("MasterData").Range("B" & i) 'placing the date in row i in the Sheet's B1 cell
    Next 'next i = next row for the master data sheet AND the next sheet as well
End Sub

This is extremely basic, highly improveable, and I can explain how it works in the comments if you don't understand.

Upvotes: 1

JGoldz75
JGoldz75

Reputation: 47

Try something like this: I would highly recommend you test this code on a seperate worksheet first with only a few "names" and "dates" Let me know if this is what you needed or if you need it to be modified!

Create a new button with the following code:

Dim ColumnA As String
Dim ColumnB As String

Range("A2").Select
Do Until IsEmpty(ActiveCell)
ColumnA = ActiveCell.Value
ColumnB = ActiveCell.Offset(columnOffset:=1).Value
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
Range("A1").Value = ColumnA
Range("B1").Value = ColumnB
ThisWorkbook.Sheets("Sheet1").Activate
ActiveCell.Offset(rowOffset:=1).Select
Loop

End Sub

Upvotes: 0

Related Questions