Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy each row from 1 Worksheet to separate Worksheets?
Worksheets(1) row 26 contains Headers
Worksheets(1) row 27 to row 282 contains data How do I copy each row to a separate worksheet automatically? -- Thank you so much for your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy each row from 1 Worksheet to separate Worksheets?
Dandelo
This code will take the headers in A26:H26 (extend if you need more width) of the data worksheet and then each row below to 282 will become the only line below the header on a new sheet, with the next row making another new sheet, so the model ends up with the original data sheet and 256 others, each containing the headers in A1:H1 and one row of data in A2:H2. The sheet tab is named with the row of the data it contains and is placed to the right of the last one added Hope I understood... (place this in a standard module and your data sheet must be the first sheet in the workbook, or you will need to change the worksheets(1) to Worksheets("NameOfYourDataSheet")) Sub MoveData() Dim wksData As Worksheet Dim wksNew As Worksheet Dim x As Long Dim vHeaders As Variant Set wksData = Worksheets(1) vHeaders = wksData.Range("A26:H26").Value x = 27 For x = 27 To 282 Set wksNew = Worksheets.Add(, Worksheets(Worksheets.Count)) wksNew.Name = "Row " & x & " Data" wksNew.Range("A1:H1").Value = vHeaders wksData.Range("A" & x & ":H" & x).Copy Destination:=wksNew.Range("A2") Set wksNew = Nothing Next x End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Dandelo" wrote in message ... Worksheets(1) row 26 contains Headers Worksheets(1) row 27 to row 282 contains data How do I copy each row to a separate worksheet automatically? -- Thank you so much for your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I copy each row from 1 Worksheet to separate Worksheets
Wow, Nick!
That worked great! Thank you so much for taking the time to answer! -- Thank you so much for your help! "Nick Hodge" wrote: Dandelo This code will take the headers in A26:H26 (extend if you need more width) of the data worksheet and then each row below to 282 will become the only line below the header on a new sheet, with the next row making another new sheet, so the model ends up with the original data sheet and 256 others, each containing the headers in A1:H1 and one row of data in A2:H2. The sheet tab is named with the row of the data it contains and is placed to the right of the last one added Hope I understood... (place this in a standard module and your data sheet must be the first sheet in the workbook, or you will need to change the worksheets(1) to Worksheets("NameOfYourDataSheet")) Sub MoveData() Dim wksData As Worksheet Dim wksNew As Worksheet Dim x As Long Dim vHeaders As Variant Set wksData = Worksheets(1) vHeaders = wksData.Range("A26:H26").Value x = 27 For x = 27 To 282 Set wksNew = Worksheets.Add(, Worksheets(Worksheets.Count)) wksNew.Name = "Row " & x & " Data" wksNew.Range("A1:H1").Value = vHeaders wksData.Range("A" & x & ":H" & x).Copy Destination:=wksNew.Range("A2") Set wksNew = Nothing Next x End Sub -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk HIS "Dandelo" wrote in message ... Worksheets(1) row 26 contains Headers Worksheets(1) row 27 to row 282 contains data How do I copy each row to a separate worksheet automatically? -- Thank you so much for your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I copy columns between worksheets if the columns don't matc | Excel Worksheet Functions | |||
Can not copy a worksheet to another workbook | Excel Discussion (Misc queries) | |||
Merge Worksheets | Excel Discussion (Misc queries) | |||
Merge Worksheets | Excel Worksheet Functions | |||
Copy worksheet with Pivot Table and break link to original workshe | Excel Worksheet Functions |