M
Midget
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
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