Rearranging Data

I have data stored in an excel sheet in the following format. Column 1
contains a location. Columns 2-3 have values for day 1, Columns 4-5 have
values for day 2, etc... One set for each day of the month. There is a
seperate worksheet for each month. I would like to automate something to put
all of the data on one sheet in only 5 columns. Column 1 Location, Column 2
Day, Column 3 Month, Column 4-5 the values. I have built this to transport
into access but it takes way to long. Any help in the most efficient way to
do this in excel?

Here's some code

Sub test()
Dim iLastRow As Long
Dim i As Long, j As Long, k As Long
Dim iRow As Long
Dim sh As Worksheet

Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Summary"
With ActiveSheet
.Range("A1").Value = "Location"
.Range("B1").Value = "Day"
.Range("C1").Value = "Month"
.Range("D1").Value = "Amt1"
.Range("E1").Value = "Amt2"
iRow = 2
For i = 1 To Worksheets.Count - 1
Set sh = Worksheets(i)
iLastRow = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
For j = 2 To iLastRow
For k = 2 To 65 Step 2
If Not IsEmpty(sh.Cells(j, k).Value) Or _
Not IsEmpty(sh.Cells(j, k + 1).Value) Then
Cells(iRow, "A").Value = sh.Cells(j, "A").Value
Cells(iRow, "B").Value = k \ 2
Cells(iRow, "C").Value = sh.Index
Cells(iRow, "D").Value = sh.Cells(j, k).Value
Cells(iRow, "E").Value = sh.Cells(j, k + 1).Value
iRow = iRow + 1
End If
Next k
Next j
Next i
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"HIT Engineering" <[email protected]> wrote in
message news:[email protected]...
> I have data stored in an excel sheet in the following format. Column 1
> contains a location. Columns 2-3 have values for day 1, Columns 4-5 have
> values for day 2, etc... One set for each day of the month. There is a
> seperate worksheet for each month. I would like to automate something to
put
> all of the data on one sheet in only 5 columns. Column 1 Location, Column
2
> Day, Column 3 Month, Column 4-5 the values. I have built this to
transport
> into access but it takes way to long. Any help in the most efficient way
to
> do this in excel?