Thanks Bernie

[Guest]

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?

 

[Guest]

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!

 

[JulieD]

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.

 

[Bernie Deitrick]

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

 

[Guest]

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!

 

[Guest]

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

 

[Bernie Deitrick]

:-\ 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

 

[Guest]

How about if there is a formula in row 7?

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