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

cctobias

New Member
Joined
May 20, 2010
Messages
6
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!!!!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

cctobias

New Member
Joined
May 20, 2010
Messages
6
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Assuming that you are using my code, you just need to click in the relevant column when prompted.
 

cctobias

New Member
Joined
May 20, 2010
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Paste the actual code here, formatted withing CODE tags, and then people can see what you're doing. Now we're just guessing.
 

cctobias

New Member
Joined
May 20, 2010
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
- Formatting code is done with tags
Code:
 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

New Member
Joined
May 20, 2010
Messages
6
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

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Use F8 to step through the code.

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

Code:
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
 

Forum statistics

Threads
1,136,968
Messages
5,678,864
Members
419,787
Latest member
juanam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top