Thanks Bernie
 

Code graciously provided by Bernie Deitrick yesterday .....

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 getting Rune Time Error 1004 on this line

Range("10:10").SpecialCells(xlCellTypeConstants)
Any ideas why?

Thanks Bernie
 

I did add option explicit and declared additional variables

Dim myCell As Range
Dim Basebook As Workbook
Dim i As Integer
Dim mybook As Workbook

But I am still getting error

Thanks!

"Jodi" wrote:

Code graciously provided by Bernie Deitrick yesterday .....

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 getting Rune Time Error 1004 on this line

Range("10:10").SpecialCells(xlCellTypeConstants)
Any ideas why?

Thanks Bernie
 

is the code you pasted your actual code from your workbook or Bernie's code
from yesterday ... if its Bernie's code, check that the line prior to the
error line on your code has a
space underscore
at the end of it
if that's not the problem and this is Bernie's code, could you please copy &
paste your code.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Jodi" wrote in message
...
I did add option explicit and declared additional variables

Dim myCell As Range
Dim Basebook As Workbook
Dim i As Integer
Dim mybook As Workbook

But I am still getting error

Thanks!

"Jodi" wrote:

Code graciously provided by Bernie Deitrick yesterday .....

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 getting Rune Time Error 1004 on this line

Range("10:10").SpecialCells(xlCellTypeConstants)
Any ideas why?

Thanks Bernie
 

Jodi,

You probably have formulas in row 10 rather than constants. Try changing
that row to

For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeFormulas)


Also, if you have a mix of formulas and constants, then you could change

For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeConstants)
If myCell.Value < "DOG" Then

to

For Each myCell In _
Intersect(Range("10:10"), ActiveSheet.UsedRange)
If myCell.Value < "" And myCell.Value < "DOG" Then

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
I did add option explicit and declared additional variables

Dim myCell As Range
Dim Basebook As Workbook
Dim i As Integer
Dim mybook As Workbook

But I am still getting error

Thanks!

"Jodi" wrote:

Code graciously provided by Bernie Deitrick yesterday .....

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 getting Rune Time Error 1004 on this line

Range("10:10").SpecialCells(xlCellTypeConstants)
Any ideas why?

Thanks Bernie
 

As copied from my workbook with different rows referenced....

Sub Consolidate()

Dim myCell As Range
Dim Basebook As Workbook
Dim i As Integer
Dim mybook As Workbook


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("4:4").SpecialCells(xlCellTypeConstants)
If myCell.Value < "DOG" Then
ThisWorkbook.Worksheets(1). _
Range("A65536").End(xlUp)(2).Value = _
myCell.Offset(328, 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

Thanks!


"JulieD" wrote:

is the code you pasted your actual code from your workbook or Bernie's code
from yesterday ... if its Bernie's code, check that the line prior to the
error line on your code has a
space underscore
at the end of it
if that's not the problem and this is Bernie's code, could you please copy &
paste your code.

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"Jodi" wrote in message
...
I did add option explicit and declared additional variables

Dim myCell As Range
Dim Basebook As Workbook
Dim i As Integer
Dim mybook As Workbook

But I am still getting error

Thanks!

"Jodi" wrote:

Code graciously provided by Bernie Deitrick yesterday .....

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 getting Rune Time Error 1004 on this line

Range("10:10").SpecialCells(xlCellTypeConstants)
Any ideas why?

Thanks Bernie
 

Ive tried both alternative and am still getting 1004
:-\

"Bernie Deitrick" wrote:

Jodi,

You probably have formulas in row 10 rather than constants. Try changing
that row to

For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeFormulas)


Also, if you have a mix of formulas and constants, then you could change

For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeConstants)
If myCell.Value < "DOG" Then

to

For Each myCell In _
Intersect(Range("10:10"), ActiveSheet.UsedRange)
If myCell.Value < "" And myCell.Value < "DOG" Then

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
I did add option explicit and declared additional variables

Dim myCell As Range
Dim Basebook As Workbook
Dim i As Integer
Dim mybook As Workbook

But I am still getting error

Thanks!

"Jodi" wrote:

Code graciously provided by Bernie Deitrick yesterday .....

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 getting Rune Time Error 1004 on this line

Range("10:10").SpecialCells(xlCellTypeConstants)
Any ideas why?

Thanks Bernie
 

:-\ indeed.

Send me a sample workbook, and I'll take a look. Remove the space and
change the dot to .

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
Ive tried both alternative and am still getting 1004
:-\

"Bernie Deitrick" wrote:

Jodi,

You probably have formulas in row 10 rather than constants. Try
changing
that row to

For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeFormulas)


Also, if you have a mix of formulas and constants, then you could change

For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeConstants)
If myCell.Value < "DOG" Then

to

For Each myCell In _
Intersect(Range("10:10"), ActiveSheet.UsedRange)
If myCell.Value < "" And myCell.Value < "DOG" Then

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
I did add option explicit and declared additional variables

Dim myCell As Range
Dim Basebook As Workbook
Dim i As Integer
Dim mybook As Workbook

But I am still getting error

Thanks!

"Jodi" wrote:

Code graciously provided by Bernie Deitrick yesterday .....

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 getting Rune Time Error 1004 on this line

Range("10:10").SpecialCells(xlCellTypeConstants)
Any ideas why?

Thanks Bernie
 

How about if there is a formula in row 7?

"Bernie Deitrick" wrote:

Jodi,

You probably have formulas in row 10 rather than constants. Try changing
that row to

For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeFormulas)


Also, if you have a mix of formulas and constants, then you could change

For Each myCell In _
Range("10:10").SpecialCells(xlCellTypeConstants)
If myCell.Value < "DOG" Then

to

For Each myCell In _
Intersect(Range("10:10"), ActiveSheet.UsedRange)
If myCell.Value < "" And myCell.Value < "DOG" Then

HTH,
Bernie
MS Excel MVP


"Jodi" wrote in message
...
I did add option explicit and declared additional variables

Dim myCell As Range
Dim Basebook As Workbook
Dim i As Integer
Dim mybook As Workbook

But I am still getting error

Thanks!

"Jodi" wrote:

Code graciously provided by Bernie Deitrick yesterday .....

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 getting Rune Time Error 1004 on this line

Range("10:10").SpecialCells(xlCellTypeConstants)
Any ideas why?