Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy a template and fill in data from a single worksheet
I'm trying to make a macro to automate a tedious process of creating product
detail forms from a summary page. Sheet 1 is the summary page containing data in columns A-N.The number of rows is unlimited. Sheet 2 is a template that would be filled in with the data from Sheet 1, but each row in Sheet 1 would get its own individual worksheet - if I were doing this manually, I would make as many copies of Sheet 2 as I have rows in Sheet 1, and fill in certain cells on those sheets with the data in columns A-N on Sheet 1. Also, the name of the worksheet would be the value in column A. For example, if Sheet 1 contains 10 rows of products, I would make 10 copies of sheet 2 - the first copy would contain the data in row 1, the second copy would contain the data in row 2, etc. Is there a way to write a macro to automate this process? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy a template and fill in data from a single worksheet
try this code:
Option Explicit 'replicate sheet 'Template' 'for each row in sheet 'Main' Sub Main() Dim RowIndex As Long 'row counter Dim shNew As Worksheet Dim source As Range RowIndex = 1 Do While Worksheets("Main").Cells(RowIndex, 1) < "" Set shNew = Worksheets.Add(Worksheets(Worksheets.Count)) shNew.Name = "row_" & Format(RowIndex, "0000") 'copy some stuff With Sheets("Main") Set source = .Range(.Cells(RowIndex, "A"), .Cells(RowIndex, "N")) End With With source shNew.Range("A2").Resize(.Columns.Count, 1).Value = _ WorksheetFunction.Transpose(.Value) End With 'end of copy section shNew.Range("a1") = "row #" & RowIndex 'next row RowIndex = RowIndex + 1 Loop End Sub "Jill" wrote: I'm trying to make a macro to automate a tedious process of creating product detail forms from a summary page. Sheet 1 is the summary page containing data in columns A-N.The number of rows is unlimited. Sheet 2 is a template that would be filled in with the data from Sheet 1, but each row in Sheet 1 would get its own individual worksheet - if I were doing this manually, I would make as many copies of Sheet 2 as I have rows in Sheet 1, and fill in certain cells on those sheets with the data in columns A-N on Sheet 1. Also, the name of the worksheet would be the value in column A. For example, if Sheet 1 contains 10 rows of products, I would make 10 copies of sheet 2 - the first copy would contain the data in row 1, the second copy would contain the data in row 2, etc. Is there a way to write a macro to automate this process? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'move or copy' a single worksheet multiple times | Excel Discussion (Misc queries) | |||
Possible to copy worksheet into template? | Excel Discussion (Misc queries) | |||
Copy Filterd Data from worksheet to another workbook template | Excel Discussion (Misc queries) | |||
Copy Range From Multiple Worksheets to a Single Worksheet | Excel Discussion (Misc queries) | |||
Copy from different workbook's worksheet into a single worksheet... | Excel Discussion (Misc queries) |