Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create new sheets based off Data sheet, and template sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create new sheets based off Data sheet, and template sheet
On Apr 30, 7:16 pm, Midget 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create new sheets based off Data sheet, and template sheet
On May 1, 6:45 am, Midget wrote:
On Apr 30, 7:16 pm, Midget 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create New Sheets and Name Them Based on Values in another sheet | Excel Discussion (Misc queries) | |||
adding sheets based upon a template sheet | Excel Worksheet Functions | |||
Seperate data from one sheet to multiple sheets based on a column | Excel Discussion (Misc queries) | |||
Create, name, open, and enter data in new sheet - from template | Excel Discussion (Misc queries) | |||
Replacing data in a row of sheet1 with data in a row of sheet 2 based on a field in both sheets being the same? | Excel Programming |