create multiple sheets using info from one

[olson_jr]

i have an equipment list which has everything from vehicle numbers to engine
horsepower. there is 38 total pieces of equipment that i need to make
individual sheets for. i created another sheet for my individuals and put in
all the formulas to pull off from the master. is there a shortcut to make 37
more. basically every row in the master sheet needs to go into an individual.

 

[Ron de Bruin]

Hi olson

Start here (for a code example)
http://www.rondebruin.nl/copy5.htm

 

[JLatham]

I think this code will do the trick for you. To put the code into your
workbook, open the workbook and press [Alt]+[F11] to open the VB Editor. In
the editor, use Insert --> Module to create an empty code module. Copy the
code below and paste it into that module and make any changes you need to for
the two Const values.

Close the VB editor, choose the sheet with your 38 entries on it and use
Tools-->Macro --> Macros to run the code.

Sub MakeNewSheets()
'be sure the 'original' sheet is selected and then
'use Tools --> Macro --> Macros to run this code
'
'change these 2 values to match the
'first and last rows you need to move to
'new, separate sheets
Const firstRow = 2
Const lastRow = 39

Dim myWS As Worksheet
Dim LC As Integer

Set myWS = ActiveSheet
For LC = firstRow To lastRow
'add new sheet at end of workbook
ThisWorkbook.Worksheets.Add after:=Worksheets(Worksheets.Count)
myWS.Range(LC & ":" & LC).Copy
ActiveSheet.Range("A2").PasteSpecial xlAll
Next
Set myWS = Nothing
End Sub