VBA or macro to loop through rows in master sheet to copy 2nd sheet with value

[cctobias]

Hello, experts! I could really use your help!

I consider myself an excel expert (can do pivot tables, formulas, vlookups in my sleep) but have never had to use macros or VBA and desperately have a situation where I need to now.

I have a master sheet of data with LOTS of rows where column A is someone's name: (ex.)
Name date dollars
Jack 4/1/90 $5
Jill 2/1/79 $1
John 3/6/00 $10

I have a second sheet which is a template of a form that I need to create for every name on the list: (ex.)
Form For
Jack
Date: 4/1/90
Dollars: $5

In the template, I have the name coming into the spreadsheet with a formula that says ='Master sheet'!A:A
The rest of the data in the template uses vlookups keying off of that name.

SO, what kind of loop statement could I create in VBA that would copy the template worksheet, and loop through each row of the master spreadsheet to create a worksheet for each person?

THANK YOU THANK YOU THANK YOU!!!!

 

[VoG]

This will do that but not in exactly the format that you want http://www.mrexcel.com/forum/showthread.php?t=396069

 

[cctobias]

Thank you SO much. Unfortunately, I'm a little too new at this to know what i need to substitute. (he is taking a specific value to name the new tab and i need to take a specific value and enter it to a specific cell in a "COPIED" worksheet of my template.

 

[VoG]

Assuming that you are using my code, you just need to click in the relevant column when prompted.

 

[cctobias]

I'm so sorry, Peter, thank you for your patience. So - I copied your code into the visual basic editor for my entire workbook. When I ran it it gave me a "Run-time error '1004' Application-defined or Object-Defined error". Any ideas?

THANKS!!!

 

[wigi]

Paste the actual code here, formatted withing CODE tags, and then people can see what you're doing. Now we're just guessing.

 

[cctobias]

Here you go:

Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
Application.ScreenUpdating = False
With ActiveSheet
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Range(.Cells(2, 1), Cells(lastrow, LastCol)).Sort Key1:=Range("H2"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
iStart = 2
For i = 2 To lastrow
If .Range("H" & i).Value <> .Range("H" & i + 1).Value Then
iEnd = i
Sheets.Add after:=Sheets(Sheets.Count)
Set ws = ActiveSheet
On Error Resume Next
ws.Name = .Range("H" & iStart).Value
On Error GoTo 0
ws.Range(Cells(1, 1), Cells(1, LastCol)).Value = .Range(.Cells(1, 1), .Cells(1, LastCol)).Value
.Range(.Cells(iStart, 1), .Cells(iEnd, LastCol)).Copy Destination:=ws.Range("A2")
iStart = iEnd + 1
End If
Next i
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[wigi]

- Formatting code is done with tags

 and [ /code] (this last one without the leading space)

 - Why didn't you change the hard-coded column H in the code? You're going to split by column A, so H is wrong.

 - What line generates the error?

 

[cctobias]

Whoo Hoo! That did something! Now I have tabs named for each of my people, but it only copied the master sheet.

Any ideas how I can get it to use the "copy tab" function and select the tab I want it to copy?

 

[wigi]

Use F8 to step through the code.

Within the loop, you'll notice that it is:

Sheets.Add after:=Sheets(Sheets.Count)

that you'll want to change. It should be copying another sheet rather than adding one. The Copy method can do this. Please check out the help files.

Wigi