Hi
I amtrying to learn VBA on my own and am making slow progress. I have been
tasked with creating a macro and I am not having any luck. This is the
scenario... There are daily spreadsheets that are stored as

G:\\Mypath\DailyMMMYYY\yymmdd.xls

At the end of the month I need to create a summary worksheet that looks at
row 10 on each daily sheet and and if the value is not "DOG" then I need to
copy the value of Row 7 of the same column to the summary sheet. The values
need to be copied to the summary sheet in a columnwise fashion. The number
of columns for each day may vary so I need to look at the entire row. I only
need the value in row 7 on the summary sheet.

Thanks!
Jodi

Jodi,

If the value is not "DOG", is it something else, or is it blank? How many
columns might need to be copied from each of the sheets? How many columns
need to be examined for values that are not "DOG"? How many sheets are in
the workbooks named yymmdd.xls (are there extra blank sheets that can be
ignored?)? What is the name of the sheet with the data?

Answer those questions, and the macro will be quite easy....

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
Hi
I amtrying to learn VBA on my own and am making slow progress. I have
been
tasked with creating a macro and I am not having any luck. This is the
scenario... There are daily spreadsheets that are stored as

G:\\Mypath\DailyMMMYYY\yymmdd.xls

At the end of the month I need to create a summary worksheet that looks at
row 10 on each daily sheet and and if the value is not "DOG" then I need
to
copy the value of Row 7 of the same column to the summary sheet. The
values
need to be copied to the summary sheet in a columnwise fashion. The
number
of columns for each day may vary so I need to look at the entire row. I
only
need the value in row 7 on the summary sheet.

Thanks!
Jodi

Hi thanks for the response!
1) Yes the value will be from a finite list. I want everything but "DOG".
2)The number of columns will vary from 4or5 to possibly 10 or 12 but its
open ended. So anything from E10:IV10 should be included in the copy.
3)There is only one sheet now (Daily!) but this is a work in progress and
there may be more sheets int he future. However, Daily! is the only sheet of
importance to this macro, all others can be ignored.
I hoe this answered all your questions
Thanks
Jodi
"Bernie Deitrick" wrote:

Jodi,

If the value is not "DOG", is it something else, or is it blank? How many
columns might need to be copied from each of the sheets? How many columns
need to be examined for values that are not "DOG"? How many sheets are in
the workbooks named yymmdd.xls (are there extra blank sheets that can be
ignored?)? What is the name of the sheet with the data?

Answer those questions, and the macro will be quite easy....

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
Hi
I amtrying to learn VBA on my own and am making slow progress. I have
been
tasked with creating a macro and I am not having any luck. This is the
scenario... There are daily spreadsheets that are stored as

G:\\Mypath\DailyMMMYYY\yymmdd.xls

At the end of the month I need to create a summary worksheet that looks at
row 10 on each daily sheet and and if the value is not "DOG" then I need
to
copy the value of Row 7 of the same column to the summary sheet. The
values
need to be copied to the summary sheet in a columnwise fashion. The
number
of columns for each day may vary so I need to look at the entire row. I
only
need the value in row 7 on the summary sheet.

Thanks!
Jodi

--
HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
Hi thanks for the response!
1) Yes the value will be from a finite list. I want everything but "DOG".
2)The number of columns will vary from 4or5 to possibly 10 or 12 but its
open ended. So anything from E10:IV10 should be included in the copy.
3)There is only one sheet now (Daily!) but this is a work in progress and
there may be more sheets int he future. However, Daily! is the only sheet
of
importance to this macro, all others can be ignored.
I hoe this answered all your questions
Thanks
Jodi
"Bernie Deitrick" wrote:

Jodi,

If the value is not "DOG", is it something else, or is it blank? How
many
columns might need to be copied from each of the sheets? How many
columns
need to be examined for values that are not "DOG"? How many sheets are
in
the workbooks named yymmdd.xls (are there extra blank sheets that can be
ignored?)? What is the name of the sheet with the data?

Answer those questions, and the macro will be quite easy....

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
Hi
I amtrying to learn VBA on my own and am making slow progress. I have
been
tasked with creating a macro and I am not having any luck. This is
the
scenario... There are daily spreadsheets that are stored as

G:\\Mypath\DailyMMMYYY\yymmdd.xls

At the end of the month I need to create a summary worksheet that
looks at
row 10 on each daily sheet and and if the value is not "DOG" then I
need
to
copy the value of Row 7 of the same column to the summary sheet. The
values
need to be copied to the summary sheet in a columnwise fashion. The
number
of columns for each day may vary so I need to look at the entire row.
I
only
need the value in row 7 on the summary sheet.

Thanks!
Jodi

Jodi,

Well, that last reply of mine was a bit terse, so I thought I should clarify
;-)

Copy the code below, and paste it into a code module in an other wise empty
workbook. Then save the workbook in the same folder (the DailyMMMYYY
folder) where your other workbooks are saved.

My assumptions:
the values in row 10 are constants - directly typed in, rather than the
results of formulas.
the sheet really is named Daily! with the exclamation point.
you want the values from row 7 to go down column A, in whatever order they
come out of the files, with no other identifiers (like the file name or
date)

Give it a try. It won't change anything in any of the stored workbooks, so
there is little danger of corrupting the files, but it may be better to try
things out on copies in a new folder, until you are sure of how it works.

HTH,
Bernie
MS Excel MVP

Sub JodiConsolidate()

Dim myCell As Range

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Copy or move this workbook to the folder with
'the files that you want to summarize
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) < ThisWorkbook.FullName Then
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets("Daily!").Select
For Each myCell In
Range("E10:IV10").SpecialCells(xlCellTypeConstants )
If myCell.Value < "DOG" Then

ThisWorkbook.Worksheets(1).Range("A65536").End(xlU p)(2).Value = _
myCell.Offset(-3, 0).Value
End If
Next myCell
myBook.Close
End If
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub

"Jodi" wrote in message
...
Hi thanks for the response!
1) Yes the value will be from a finite list. I want everything but "DOG".
2)The number of columns will vary from 4or5 to possibly 10 or 12 but its
open ended. So anything from E10:IV10 should be included in the copy.
3)There is only one sheet now (Daily!) but this is a work in progress and
there may be more sheets int he future. However, Daily! is the only sheet
of
importance to this macro, all others can be ignored.
I hoe this answered all your questions
Thanks
Jodi
"Bernie Deitrick" wrote:

Jodi,

If the value is not "DOG", is it something else, or is it blank? How
many
columns might need to be copied from each of the sheets? How many
columns
need to be examined for values that are not "DOG"? How many sheets are
in
the workbooks named yymmdd.xls (are there extra blank sheets that can be
ignored?)? What is the name of the sheet with the data?

Answer those questions, and the macro will be quite easy....

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
Hi
I amtrying to learn VBA on my own and am making slow progress. I have
been
tasked with creating a macro and I am not having any luck. This is
the
scenario... There are daily spreadsheets that are stored as

G:\\Mypath\DailyMMMYYY\yymmdd.xls

At the end of the month I need to create a summary worksheet that
looks at
row 10 on each daily sheet and and if the value is not "DOG" then I
need
to
copy the value of Row 7 of the same column to the summary sheet. The
values
need to be copied to the summary sheet in a columnwise fashion. The
number
of columns for each day may vary so I need to look at the entire row.
I
only
need the value in row 7 on the summary sheet.

Thanks!
Jodi

Jodi,

The macro line-wrapped badly, so try this version, below.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()

Dim myCell As Range

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Copy or move this workbook to the folder with
'the files that you want to summarize
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) < ThisWorkbook.FullName Then
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets("Daily!").Select
For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeConstants)
If myCell.Value < "DOG" Then
ThisWorkbook.Worksheets(1). _
Range("A65536").End(xlUp)(2).Value = _
myCell.Offset(-3, 0).Value
End If
Next myCell
myBook.Close
End If
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub

Hey Bernie thanks!
I copied as you suggested and only made row number modification (ie.
Range("10:10").SpecialCells(xlCellTypeConstants)
became
Range("4:4").SpecialCells(xlCellTypeConstants)
Im getting Error 1004

Im not sure what is wrong.
Thanks
Jodi

"Bernie Deitrick" wrote:

Jodi,

The macro line-wrapped badly, so try this version, below.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()

Dim myCell As Range

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Copy or move this workbook to the folder with
'the files that you want to summarize
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) < ThisWorkbook.FullName Then
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets("Daily!").Select
For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeConstants)
If myCell.Value < "DOG" Then
ThisWorkbook.Worksheets(1). _
Range("A65536").End(xlUp)(2).Value = _
myCell.Offset(-3, 0).Value
End If
Next myCell
myBook.Close
End If
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub

Im sorry---It appears that it is this line that is the one causing the problem
Range("4:4").SpecialCells(xlCellTypeConstants)
and the values are entered via data validation not a formula if that makes a
difference
Thanks

"Bernie Deitrick" wrote:

Jodi,

The macro line-wrapped badly, so try this version, below.

HTH,
Bernie
MS Excel MVP

Sub Consolidate()

Dim myCell As Range

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With

With Application.FileSearch
.NewSearch
'Copy or move this workbook to the folder with
'the files that you want to summarize
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set Basebook = ThisWorkbook
For i = 1 To .FoundFiles.Count
If .FoundFiles(i) < ThisWorkbook.FullName Then
Set myBook = Workbooks.Open(.FoundFiles(i))
myBook.Worksheets("Daily!").Select
For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeConstants)
If myCell.Value < "DOG" Then
ThisWorkbook.Worksheets(1). _
Range("A65536").End(xlUp)(2).Value = _
myCell.Offset(-3, 0).Value
End If
Next myCell
myBook.Close
End If
Next i
End If
End With

With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With

Basebook.SaveAs Application.GetSaveAsFilename

End Sub