On daily basis, I work with files that I need to extract data for specific time (04:00, 08:00, 12:00, 16:00, 20:00 and 00:00). "00:00" is for today and the rest of hours go back to yesterday timing (in retrospect). The previous code from web-page "Re: 2-Digit Year" was required to format dates (see screen-shot 02).
In the 3rd screen-shot shown the data I need to pull from each page of a workbook. It is highlighted in green, in the column "I" for the hours 04:00, 08:00, 12:00, 16:00, 20:00 and 00:00 (see screen-shot 03 and screen-shot 05). How can I make a proper macro that loops through entire workbook, copies the required data and pastes (transpose) it in a separate worksheet with name of each worksheet corresponding? See screenshot-4 for clarification (the way I need the data organized).
I tried to do the combination of these macros. But it makes only half of the required task. Is there a way to make it fully automatic? I am using MS Excel 2010.
Here are posted the screenshots.
https://picasaweb.google.com/bamerand/April212013#5869087256991361474
https://picasaweb.google.com/bamerand/April212013#5869087272939589410
https://picasaweb.google.com/bamerand/April212013#5869087268998133922
https://picasaweb.google.com/bamerand/April212013#5869087305766003106
https://picasaweb.google.com/bamerand/April212013#5869087321868604802
Sub Select_Hours()
Selection.AutoFilter Field:=1, Criteria1:=Array("00:00", "04:00", "08:00", "12:00", "16:00", _
"20:00", "4:00", "8:00", "0:00"), Operator:=xlFilterValues
With Selection
.Interior.Color = 65535
End With
End Sub
Sub GoToDate()
Dim MyRow As Long
With Worksheets("120")
MyRow = .Columns("A").Find(Date).Row
Application.Goto .Range("A" & MyRow), True
End With
End Sub
Sub ChDates()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
For i = 1 To LR
With Range("A" & i)
.NumberFormat = "dd/mm/yyyy"
.Value = DateValue(.Value)
End With
Next i
End Sub
In the 3rd screen-shot shown the data I need to pull from each page of a workbook. It is highlighted in green, in the column "I" for the hours 04:00, 08:00, 12:00, 16:00, 20:00 and 00:00 (see screen-shot 03 and screen-shot 05). How can I make a proper macro that loops through entire workbook, copies the required data and pastes (transpose) it in a separate worksheet with name of each worksheet corresponding? See screenshot-4 for clarification (the way I need the data organized).
I tried to do the combination of these macros. But it makes only half of the required task. Is there a way to make it fully automatic? I am using MS Excel 2010.
Here are posted the screenshots.
https://picasaweb.google.com/bamerand/April212013#5869087256991361474
https://picasaweb.google.com/bamerand/April212013#5869087272939589410
https://picasaweb.google.com/bamerand/April212013#5869087268998133922
https://picasaweb.google.com/bamerand/April212013#5869087305766003106
https://picasaweb.google.com/bamerand/April212013#5869087321868604802
Sub Select_Hours()
Selection.AutoFilter Field:=1, Criteria1:=Array("00:00", "04:00", "08:00", "12:00", "16:00", _
"20:00", "4:00", "8:00", "0:00"), Operator:=xlFilterValues
With Selection
.Interior.Color = 65535
End With
End Sub
Sub GoToDate()
Dim MyRow As Long
With Worksheets("120")
MyRow = .Columns("A").Find(Date).Row
Application.Goto .Range("A" & MyRow), True
End With
End Sub
Sub ChDates()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
For i = 1 To LR
With Range("A" & i)
.NumberFormat = "dd/mm/yyyy"
.Value = DateValue(.Value)
End With
Next i
End Sub