Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5


Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!



"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson

More explanation: Ultimately the end result would be all of the monthly
columns below each row (add 11 rows below each existing row)...then add a
column that is titled "DATA" this would contain the data that is currently
in monthly columns AND a column that contains the MONTH (Jan-Dec for each
existing row). Is there something I can do by bringing it into Access?
Bottom line: Monthly columns...need to be transposed to Monthly ROWS...so,
11 added below each existing row.
Thanks again....

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi

I think if you modify these 3 lines in Dave's code, it does what you
want

Change the present value of 1 to iRow in each case, as below

NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value

--
Regards

Roger Govier


"deeds" wrote in message
...
More explanation: Ultimately the end result would be all of the
monthly
columns below each row (add 11 rows below each existing row)...then
add a
column that is titled "DATA" this would contain the data that is
currently
in monthly columns AND a column that contains the MONTH (Jan-Dec for
each
existing row). Is there something I can do by bringing it into
Access?
Bottom line: Monthly columns...need to be transposed to Monthly
ROWS...so,
11 added below each existing row.
Thanks again....

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I
have included
a better example below... Think of it this way....all I want to do
is bring
the months down as a row below each Title1,Title2,Title3
combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12
rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH
& DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow,
.Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1,
"A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1,
"B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1,
"C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1,
iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow,
iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's
intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep
Oct Nov Dec
A B C 5 6 7 8 9 10
11 12 13
14 15 16

I want to bring the months and the data down as rows underneath
the ABC
group, now I do have about 1500 rows like this. So, ultimately
I need to add
12 rows underneath each current row, add the months in a column
and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson

Absolutely perfect! Thanks a ton!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Thanks! It works!

"Roger Govier" wrote:

Hi

I think if you modify these 3 lines in Dave's code, it does what you
want

Change the present value of 1 to iRow in each case, as below

NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value

--
Regards

Roger Govier


"deeds" wrote in message
...
More explanation: Ultimately the end result would be all of the
monthly
columns below each row (add 11 rows below each existing row)...then
add a
column that is titled "DATA" this would contain the data that is
currently
in monthly columns AND a column that contains the MONTH (Jan-Dec for
each
existing row). Is there something I can do by bringing it into
Access?
Bottom line: Monthly columns...need to be transposed to Monthly
ROWS...so,
11 added below each existing row.
Thanks again....

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I
have included
a better example below... Think of it this way....all I want to do
is bring
the months down as a row below each Title1,Title2,Title3
combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12
rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH
& DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow,
.Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1,
"A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1,
"B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1,
"C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1,
iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow,
iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's
intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep
Oct Nov Dec
A B C 5 6 7 8 9 10
11 12 13
14 15 16

I want to bring the months and the data down as rows underneath
the ABC
group, now I do have about 1500 rows like this. So, ultimately
I need to add
12 rows underneath each current row, add the months in a column
and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson

Sorry....one more thing...what if I had let's say 3 sets of Jan-Dec data that
I wanted to show as "Data", "Data1", "Data2". Keeping the month column the
same but adding more columns of data to the right of the current "Data"
column. What would I need to change in the code to handle that?
Thanks again!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

As long as the data is laid out nicely (January's are every 12th column, ...):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 7).Value _
= Array("title1", "title2", "title3", "month", _
"data1", "data2", "data3")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To FirstCol + (12 - 1) '11 more columns
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow, "F").Value = .Cells(iRow, iCol + 12).Value
NewWks.Cells(oRow, "G").Value = .Cells(iRow, iCol + 24).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

Sorry....one more thing...what if I had let's say 3 sets of Jan-Dec data that
I wanted to show as "Data", "Data1", "Data2". Keeping the month column the
same but adding more columns of data to the right of the current "Data"
column. What would I need to change in the code to handle that?
Thanks again!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Outstanding! Thanks again!

"Dave Peterson" wrote:

As long as the data is laid out nicely (January's are every 12th column, ...):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 7).Value _
= Array("title1", "title2", "title3", "month", _
"data1", "data2", "data3")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To FirstCol + (12 - 1) '11 more columns
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow, "F").Value = .Cells(iRow, iCol + 12).Value
NewWks.Cells(oRow, "G").Value = .Cells(iRow, iCol + 24).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

Sorry....one more thing...what if I had let's say 3 sets of Jan-Dec data that
I wanted to show as "Data", "Data1", "Data2". Keeping the month column the
same but adding more columns of data to the right of the current "Data"
column. What would I need to change in the code to handle that?
Thanks again!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

One more thing....I am now at the point where I need to make sure the data is
formated correctly. Looking back (posts) at the code I am using...how/where
do I specify the format of the data. i.e. I need the data that is currently
a number converted to text....but I need it in 3 character length. (14= 014)
Thanks again.

"deeds" wrote:

Thanks! It works!

"Roger Govier" wrote:

Hi

I think if you modify these 3 lines in Dave's code, it does what you
want

Change the present value of 1 to iRow in each case, as below

NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value

--
Regards

Roger Govier


"deeds" wrote in message
...
More explanation: Ultimately the end result would be all of the
monthly
columns below each row (add 11 rows below each existing row)...then
add a
column that is titled "DATA" this would contain the data that is
currently
in monthly columns AND a column that contains the MONTH (Jan-Dec for
each
existing row). Is there something I can do by bringing it into
Access?
Bottom line: Monthly columns...need to be transposed to Monthly
ROWS...so,
11 added below each existing row.
Thanks again....

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I
have included
a better example below... Think of it this way....all I want to do
is bring
the months down as a row below each Title1,Title2,Title3
combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12
rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH
& DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow,
.Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1,
"A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1,
"B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1,
"C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1,
iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow,
iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's
intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep
Oct Nov Dec
A B C 5 6 7 8 9 10
11 12 13
14 15 16

I want to bring the months and the data down as rows underneath
the ABC
group, now I do have about 1500 rows like this. So, ultimately
I need to add
12 rows underneath each current row, add the months in a column
and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson

One more thing....I am now at the point where I need to make sure the data is
formated correctly. Looking back (posts) at the code I am using...how/where
do I specify the format of the data. i.e. I need the data that is currently
a number converted to text....but I need it in 3 character length. (14= 014)
Thanks again.


"Dave Peterson" wrote:

As long as the data is laid out nicely (January's are every 12th column, ...):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 7).Value _
= Array("title1", "title2", "title3", "month", _
"data1", "data2", "data3")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To FirstCol + (12 - 1) '11 more columns
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow, "F").Value = .Cells(iRow, iCol + 12).Value
NewWks.Cells(oRow, "G").Value = .Cells(iRow, iCol + 24).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

Sorry....one more thing...what if I had let's say 3 sets of Jan-Dec data that
I wanted to show as "Data", "Data1", "Data2". Keeping the month column the
same but adding more columns of data to the right of the current "Data"
column. What would I need to change in the code to handle that?
Thanks again!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You can change one of these lines:

NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
to
NewWks.Cells(oRow, "D").numberformat = "@" 'text
NewWks.Cells(oRow, "D").Value = format(.Cells(1, iCol).Value,"000")

or
NewWks.Cells(oRow, "D").Value = "'" & format(.Cells(1, iCol).Value,"000")





deeds wrote:

One more thing....I am now at the point where I need to make sure the data is
formated correctly. Looking back (posts) at the code I am using...how/where
do I specify the format of the data. i.e. I need the data that is currently
a number converted to text....but I need it in 3 character length. (14= 014)
Thanks again.

"Dave Peterson" wrote:

As long as the data is laid out nicely (January's are every 12th column, ...):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 7).Value _
= Array("title1", "title2", "title3", "month", _
"data1", "data2", "data3")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To FirstCol + (12 - 1) '11 more columns
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow, "F").Value = .Cells(iRow, iCol + 12).Value
NewWks.Cells(oRow, "G").Value = .Cells(iRow, iCol + 24).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

Sorry....one more thing...what if I had let's say 3 sets of Jan-Dec data that
I wanted to show as "Data", "Data1", "Data2". Keeping the month column the
same but adding more columns of data to the right of the current "Data"
column. What would I need to change in the code to handle that?
Thanks again!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Thanks again....however, I do need the data in "text" format. will this do
it? or do I need to specify it? Thanks.

"Dave Peterson" wrote:

You can change one of these lines:

NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
to
NewWks.Cells(oRow, "D").numberformat = "@" 'text
NewWks.Cells(oRow, "D").Value = format(.Cells(1, iCol).Value,"000")

or
NewWks.Cells(oRow, "D").Value = "'" & format(.Cells(1, iCol).Value,"000")





deeds wrote:

One more thing....I am now at the point where I need to make sure the data is
formated correctly. Looking back (posts) at the code I am using...how/where
do I specify the format of the data. i.e. I need the data that is currently
a number converted to text....but I need it in 3 character length. (14= 014)
Thanks again.

"Dave Peterson" wrote:

As long as the data is laid out nicely (January's are every 12th column, ...):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 7).Value _
= Array("title1", "title2", "title3", "month", _
"data1", "data2", "data3")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To FirstCol + (12 - 1) '11 more columns
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow, "F").Value = .Cells(iRow, iCol + 12).Value
NewWks.Cells(oRow, "G").Value = .Cells(iRow, iCol + 24).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

Sorry....one more thing...what if I had let's say 3 sets of Jan-Dec data that
I wanted to show as "Data", "Data1", "Data2". Keeping the month column the
same but adding more columns of data to the right of the current "Data"
column. What would I need to change in the code to handle that?
Thanks again!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Sorry....jumped the gun (on my last reply). This works GREAT! Thanks so much!

"Dave Peterson" wrote:

You can change one of these lines:

NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
to
NewWks.Cells(oRow, "D").numberformat = "@" 'text
NewWks.Cells(oRow, "D").Value = format(.Cells(1, iCol).Value,"000")

or
NewWks.Cells(oRow, "D").Value = "'" & format(.Cells(1, iCol).Value,"000")





deeds wrote:

One more thing....I am now at the point where I need to make sure the data is
formated correctly. Looking back (posts) at the code I am using...how/where
do I specify the format of the data. i.e. I need the data that is currently
a number converted to text....but I need it in 3 character length. (14= 014)
Thanks again.

"Dave Peterson" wrote:

As long as the data is laid out nicely (January's are every 12th column, ...):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 7).Value _
= Array("title1", "title2", "title3", "month", _
"data1", "data2", "data3")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To FirstCol + (12 - 1) '11 more columns
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow, "F").Value = .Cells(iRow, iCol + 12).Value
NewWks.Cells(oRow, "G").Value = .Cells(iRow, iCol + 24).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

Sorry....one more thing...what if I had let's say 3 sets of Jan-Dec data that
I wanted to show as "Data", "Data1", "Data2". Keeping the month column the
same but adding more columns of data to the right of the current "Data"
column. What would I need to change in the code to handle that?
Thanks again!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Just to add...

The first suggestion actually changed the format of the cell to text.

The second option inserted a leading apostrophe so that the value would be
treated as text--but the cell's format wasn't changed.

deeds wrote:

Sorry....jumped the gun (on my last reply). This works GREAT! Thanks so much!

"Dave Peterson" wrote:

You can change one of these lines:

NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
to
NewWks.Cells(oRow, "D").numberformat = "@" 'text
NewWks.Cells(oRow, "D").Value = format(.Cells(1, iCol).Value,"000")

or
NewWks.Cells(oRow, "D").Value = "'" & format(.Cells(1, iCol).Value,"000")





deeds wrote:

One more thing....I am now at the point where I need to make sure the data is
formated correctly. Looking back (posts) at the code I am using...how/where
do I specify the format of the data. i.e. I need the data that is currently
a number converted to text....but I need it in 3 character length. (14= 014)
Thanks again.

"Dave Peterson" wrote:

As long as the data is laid out nicely (January's are every 12th column, ...):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 7).Value _
= Array("title1", "title2", "title3", "month", _
"data1", "data2", "data3")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To FirstCol + (12 - 1) '11 more columns
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
NewWks.Cells(oRow, "F").Value = .Cells(iRow, iCol + 12).Value
NewWks.Cells(oRow, "G").Value = .Cells(iRow, iCol + 24).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

Sorry....one more thing...what if I had let's say 3 sets of Jan-Dec data that
I wanted to show as "Data", "Data1", "Data2". Keeping the month column the
same but adding more columns of data to the right of the current "Data"
column. What would I need to change in the code to handle that?
Thanks again!

"Dave Peterson" wrote:

It was a typo (and bad test data):

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(iRow, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(iRow, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(iRow, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub



deeds wrote:

It seemed to put the "title 1" etc... all the way down the column. What I
want is the title 1 to stay put and repeat the item below it 11 times, etc...
does that help?
Thanks again

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

You could use a little macro:

Option Explicit
Sub testme01()

Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long

Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add

NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")

oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4

For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


deeds wrote:

Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16

I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".

Let me know if you need more information...THANKS!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson