I am new to scripting in excel and need some help.

I have a sheet where there is data in a cell for each hour of the day
repeating for each day of the month (in a column). I need to copy the data
for each day into a separate worksheet (in a column) then the next day into
another column.

I have done this using a recorded macro but this doesn't always work
correctly as the months have different numbers of days.

The data is always for the previous month from the current month.

Any help or advise much appreciated.

One approach is to standardize the column. For example:

A month like July has 31 days. If you have hourly sample for 24 hours, you
would have 744 samples for July (if you only sample over 8 hours, then 248
samples).

Even though a month like June has only 30 days, leave room in the data for
the missing 31st day. You would leave that day's data empty.

In that way you could copy/paste a fixed number of cells for all months.
--
Gary's Student


"Mike Hann" wrote:

I am new to scripting in excel and need some help.

I have a sheet where there is data in a cell for each hour of the day
repeating for each day of the month (in a column). I need to copy the data
for each day into a separate worksheet (in a column) then the next day into
another column.

I have done this using a recorded macro but this doesn't always work
correctly as the months have different numbers of days.

The data is always for the previous month from the current month.

Any help or advise much appreciated.

Hi Mike

This routine, if I have acknowledged the structure of your spreadsheet
should do the trick.
It will Copy column for Column into a destination sheet in my example
until it reaches the last header label in the 'Home' sheet.

See if this might work for you.

Any problems let me know and I'll help you out further.

:=====================

Sub CopyVariableNumberColumns()

Sheets("Main").Select
[A1].Select
i = 1

Do Until IsEmpty(ActiveCell)
Columns(i).copy Sheets("Destination").Columns(i)
i = i + 1
ActiveCell.Offset(0, 1).Select
Loop

End Sub

:======================

somethinglikeant
http://www.excel-ant.co.uk

I don't have control of the initial data creation as from a 3rd party
application.

The data is as such:

Day Hour Data
1 0 60
1 1 55
1 2 41
1 3 36
etc.
1 21 5
1 22 5
1 23 42
2 0 28
2 1 3
2 2 7
This repeats for each day of the month.

I need to copy the data to a new sheet as:

1st 2nd 3rd 4th etc.
0 60 45
1 55 45
2 41 65
3 36 34
4 67 54
5 92 76
6 52 34
7 42 34
8 35 43
9 17 13
10 27 20
11 13 9
12 20 9
13 9 7
14 9 5
15 7 4
16 5 3
17 4 4
18 3 4
19 4 5
20 4 5
21 5 6
22 5 12
23 42 56

"Mike Hann" wrote:

I am new to scripting in excel and need some help.

I have a sheet where there is data in a cell for each hour of the day
repeating for each day of the month (in a column). I need to copy the data
for each day into a separate worksheet (in a column) then the next day into
another column.

I have done this using a recorded macro but this doesn't always work
correctly as the months have different numbers of days.

The data is always for the previous month from the current month.

Any help or advise much appreciated.

Hi Mike,
So you have
24 cells to copy to column1, then
24 cells to column 2
etc.... while there is still data

Sub CopyDailyData()
'initialise the counters
Dim ReadRow, ReadCol, WriteRow, WriteCol, MyMod As Integer
ReadRow = 1
ReadCol = 1
WriteRow = 1
WriteCol = 1

'loop while the cells are not empty
Do While Cells(ReadRow, ReadCol) < "" 'replace 2 with the column that
has your data
'copy data to the other column
Sheet2.Cells(WriteRow, WriteCol) = Sheet1.Cells(ReadRow,
ReadCol).Value
'increment the counters
ReadRow = ReadRow + 1
WriteRow = WriteRow + 1
MyMod = ReadRow Mod 24 'every 24th row will have a Mod of 0
If MyMod = 1 Then 'if it's the 24th hour, start a new column, and
row at 1.
WriteCol = WriteCol + 1
WriteRow = 1
End If
Loop
End Sub


"Mike Hann" wrote:

I am new to scripting in excel and need some help.

I have a sheet where there is data in a cell for each hour of the day
repeating for each day of the month (in a column). I need to copy the data
for each day into a separate worksheet (in a column) then the next day into
another column.

I have done this using a recorded macro but this doesn't always work
correctly as the months have different numbers of days.

The data is always for the previous month from the current month.

Any help or advise much appreciated.

Cheers for the insight into the structure

This should do the trick

:======================

Sub CopyData()

i = 1: j = 1
Sheets("Home").Select
[A2].Select

Do Until IsEmpty(ActiveCell)

Do Until ActiveCell i Or IsEmpty(ActiveCell)
Sheets("Destination").Cells(j + 1, i + 1) = _
ActiveCell.Offset(0, 2).Value
j = j + 1
ActiveCell.Offset(1, 0).Select
Loop

i = i + 1: j = 1
Loop

End Sub

:==============================

http://www.excel-ant.co.uk