I currently have a database which includes 6 columns and several
thousand rows. I have created a template for this data to be input
into, and I have been manually typing the data, and copying the
template, renaming it, adding data, then the process starts all over.
I have found the following code which takes the first column of my
data, and creates a new worksheet (copied from template) then changes
the WS name to the data in the first column.

Public Sub CopyData()
' Determine how many CBS CODES are on Data Sheet
FinalRow = Range("A65000").End(xlUp).Row
'Loop through each CBS CODE on the data sheet
For x = 1 To FinalRow
LastSheet = Sheets.Count
Sheets("DATA").Select
CBS = Range("A" & x).Value
'Make a copy of TEMPLATE and move to end
Sheets("TEMPLATE").Copy After:=Sheets(LastSheet)
'Rename the Sheet
Sheets(LastSheet + 1).Name = CBS
Next x

End Sub

I would also like to place the other data in the row in it's
appropriate spot in the new worksheet. Being new to macros and vba, I
am finding it difficult to create a macro that will actually do this
for me. Here is an example of my current spreadsheet...

A B C
D E F
1 177.2.2 PC 10C.01 EXCAVATE 21,536.00
21,536.00 CY
2 177.2.3 PC 10C.02 RAMPS 67,804.00
67,804.00 SY

A1 - Becomes the Spreadsheet name
B1 - Becomes Bid Item in Cell D11 of Sheet 177.2.2
C1 - Becomes Description in Cell H11 of Sheet 177.2.2
D1 - Becomes Forecast Qty in Cell Y11 of Sheet 177.2.2
E1 - Becomes Owner Qty in Cell AE11 of Sheet 177.2.2
F1 - Becomes Unit of measure in Cell AI11 of Sheet 177.2.2
Row 2 then creates a New sheet called 177.2.3 and the process starts
all over.

I have approx 3000 items that need sheets generated, so you can see
why I am looking for an easier solution.
Thank you so much in advance!

Ryan

On Apr 30, 7:16 pm, Midget <rya...@cox.net> wrote:
> I currently have a database which includes 6 columns and several
> thousand rows. I have created a template for this data to be input
> into, and I have been manually typing the data, and copying the
> template, renaming it, adding data, then the process starts all over.
> I have found the following code which takes the first column of my
> data, and creates a new worksheet (copied from template) then changes
> the WS name to the data in the first column.
>
> Public Sub CopyData()
> ' Determine how many CBS CODES are on Data Sheet
> FinalRow = Range("A65000").End(xlUp).Row
> 'Loop through each CBS CODE on the data sheet
> For x = 1 To FinalRow
> LastSheet = Sheets.Count
> Sheets("DATA").Select
> CBS = Range("A" & x).Value
> 'Make a copy of TEMPLATE and move to end
> Sheets("TEMPLATE").Copy After:=Sheets(LastSheet)
> 'Rename the Sheet
> Sheets(LastSheet + 1).Name = CBS
> Next x
>
> End Sub
>
> I would also like to place the other data in the row in it's
> appropriate spot in the new worksheet. Being new to macros and vba, I
> am finding it difficult to create a macro that will actually do this
> for me. Here is an example of my current spreadsheet...
>
> A B C
> D E F
> 1 177.2.2 PC 10C.01 EXCAVATE 21,536.00
> 21,536.00 CY
> 2 177.2.3 PC 10C.02 RAMPS 67,804.00
> 67,804.00 SY
>
> A1 - Becomes the Spreadsheet name
> B1 - Becomes Bid Item in Cell D11 of Sheet 177.2.2
> C1 - Becomes Description in Cell H11 of Sheet 177.2.2
> D1 - Becomes Forecast Qty in Cell Y11 of Sheet 177.2.2
> E1 - Becomes Owner Qty in Cell AE11 of Sheet 177.2.2
> F1 - Becomes Unit of measure in Cell AI11 of Sheet 177.2.2
> Row 2 then creates a New sheet called 177.2.3 and the process starts
> all over.
>
> I have approx 3000 items that need sheets generated, so you can see
> why I am looking for an easier solution.
> Thank you so much in advance!
>
> Ryan

Any thoughts on this?

On May 1, 6:45 am, Midget <rya...@cox.net> wrote:
> On Apr 30, 7:16 pm, Midget <rya...@cox.net> wrote:
>
>
>
> > I currently have a database which includes 6 columns and several
> > thousand rows. I have created a template for this data to be input
> > into, and I have been manually typing the data, and copying the
> > template, renaming it, adding data, then the process starts all over.
> > I have found the following code which takes the first column of my
> > data, and creates a new worksheet (copied from template) then changes
> > the WS name to the data in the first column.
>
> > Public Sub CopyData()
> > ' Determine how many CBS CODES are on Data Sheet
> > FinalRow = Range("A65000").End(xlUp).Row
> > 'Loop through each CBS CODE on the data sheet
> > For x = 1 To FinalRow
> > LastSheet = Sheets.Count
> > Sheets("DATA").Select
> > CBS = Range("A" & x).Value
> > 'Make a copy of TEMPLATE and move to end
> > Sheets("TEMPLATE").Copy After:=Sheets(LastSheet)
> > 'Rename the Sheet
> > Sheets(LastSheet + 1).Name = CBS
> > Next x
>
> > End Sub
>
> > I would also like to place the other data in the row in it's
> > appropriate spot in the new worksheet. Being new to macros and vba, I
> > am finding it difficult to create a macro that will actually do this
> > for me. Here is an example of my current spreadsheet...
>
> > A B C
> > D E F
> > 1 177.2.2 PC 10C.01 EXCAVATE 21,536.00
> > 21,536.00 CY
> > 2 177.2.3 PC 10C.02 RAMPS 67,804.00
> > 67,804.00 SY
>
> > A1 - Becomes the Spreadsheet name
> > B1 - Becomes Bid Item in Cell D11 of Sheet 177.2.2
> > C1 - Becomes Description in Cell H11 of Sheet 177.2.2
> > D1 - Becomes Forecast Qty in Cell Y11 of Sheet 177.2.2
> > E1 - Becomes Owner Qty in Cell AE11 of Sheet 177.2.2
> > F1 - Becomes Unit of measure in Cell AI11 of Sheet 177.2.2
> > Row 2 then creates a New sheet called 177.2.3 and the process starts
> > all over.
>
> > I have approx 3000 items that need sheets generated, so you can see
> > why I am looking for an easier solution.
> > Thank you so much in advance!
>
> > Ryan
>
> Any thoughts on this?

BUMP