Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,173
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default 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!




  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,173
Default How do I copy each row from 1 Worksheet to separate Worksheets

No problem, thanks for coming back to say thank you :-) doesn't happen that
much now :-(

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dandelo" wrote in message
...
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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I copy columns between worksheets if the columns don't matc Miriam Excel Worksheet Functions 10 June 12th 06 04:29 PM
Can not copy a worksheet to another workbook cyndi Excel Discussion (Misc queries) 2 November 10th 05 07:50 PM
Merge Worksheets Mark Jackson Excel Discussion (Misc queries) 1 June 9th 05 10:39 AM
Merge Worksheets Mark Jackson Excel Worksheet Functions 0 June 8th 05 10:42 PM
Copy worksheet with Pivot Table and break link to original workshe setter-lover Excel Worksheet Functions 0 November 18th 04 10:29 PM


All times are GMT +1. The time now is 06:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"