Run Time Error 438: Object doesn't support this property or method.

[csenor]

This code is suppose to pull data from other worksheets and combine them all into one. Each workbook that this macro is looking through has an "invoice" sheet and "data" sheet. All I need are the records on the "data" sheet. When I step through the code and get to For Each WSN In WBN.Worksheets("Data") I get the Run Time Error. Can anybody help? This code is from Mr Excel Live Lessons.

Sub CombineFiles()
Dim WBO As Workbook ' original workbook
Dim WBN As Workbook ' individual data workbooks
Dim WSL As Worksheet ' List of files worksheet
Dim WSG As Worksheet ' data gathering worksheet
Dim WSN As Worksheet

'Define Object variables
Set WBO = ThisWorkbook
Set WSL = WBO.Worksheets("List")
Set WSG = WBO.Worksheets("Gather")

Application.DisplayAlerts = False

'Clear out any previous data on WSD, but leave the headings
WSG.Cells(2, 1).Resize(Rows.Count - 1, Columns.Count).Clear

NextRow = 2

'Loop through all the files on WSL
FinalRow = WSL.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To FinalRow
ThisFile = WSL.Cells(i, 1)
'Open a file

Set WBN = Workbooks.Open(Filename:=ThisFile)

For Each WSN In WBN.Worksheets("Data")
'Last row in this file? Rowcount is 1 less
LastRow = WSN.Cells(Rows.Count, 1).End(xlUp).Row
RowCount = LastRow - 1

'Copy from 5 to last row over to column B
WSN.Cells(2, 1).Resize(RowCount, 17).Copy _
Destination:=WSG.Cells(NextRow, 1)
'Replicate the department from A2 to column A in WSD
'WSD.Cells(NextRow, 1).Resize(RowCount, 1).Value _
' = WSN.Range("A2")

'Set up the new NextRow
NextRow = NextRow + RowCount
Next WSN

'Close WSN don't save
WBN.Close SaveChanges:=False

Next i

Application.DisplayAlerts = True

End Sub

 

[ChrisM]

You've got a critical logic error:

Dim WSN As Worksheet
...
...
For Each WSN In WBN.Worksheets("Data")

So here you are trying to loop through every Worksheet on the "Data" worksheet. Obviously worksheets don't contain other worksheets, so what are you trying to accomplish in the loop? Do you want to loop through every worksheet in the workbook, or loop through every cell in the Data sheet?

 

[Norie]

Why are you looping worksheets when you know the specific worksheet of interest, Data?

Change this,

For Each WSN In WBN.Worksheets("Data")

to this,

Set WSN = WBN.Worksheets("Data")

and remove Next WSN.

 

[Norie]

Why are you looping worksheets when you know the specific worksheet of interest, Data?

Change this,

For Each WSN In WBN.Worksheets("Data")

to this,

Set WSN = WBN.Worksheets("Data")

and remove Next WSN.

 

[csenor]

I have a collection of invoice workbooks from different departments. I have a macro that pulls the information I want to keep a database of and puts it on a second sheet called "Data". So I put all of these invoices in one folder on my computer. I then open up a workbook and run a macro that will list every file in that folder to WSL, then run another macro that will copy and paste the content of each "Data" worksheet one by one to WSG.

Norie, I will give it a try and let you know. Thanks.

 

[csenor]

Norie,

I made the change you suggested and it worked. Thanks. The only problem I'm having now is that when it calls each file and opens it, I get a notification box saying "that the file is linked to another file, Do I want to update?" I don't know what this is. I don't get the message when I manually open up each file. Only when the macro opens it. As long as this notification box keeps appearing, the process isn't going to be automated. I'll have to answer the notification box each time. Another member suggested I put ActiveWorkbook.BreakLink Name:="Cash for Gold.xlsm", Type:=xlExcelLinks . I added it to the macro that saves the file and it still doesn't make a difference. Here is the code:

Sub CashforGoldDataReport()
'Declare Variables
Dim WSD As Worksheet ' Data worksheet
Dim WSI As Worksheet ' Invoice
Dim NextRow As Long, I As Integer
Dim NewFN As Variant
Dim sWS As String
Dim lngTopRow As Long, Pic
Set WSI = Worksheets("Invoice")
Set WSD = Worksheets("Data")
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
'Set Starting Value of NextRow Variable
NextRow = 2


'Activate Invoice Tab
WSI.Activate


'Data Report Transpose
Range("F5:F8").Copy
Sheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=True
Range("B6:B14").Copy
Sheets("Data").Range("E2").PasteSpecial Paste:=xlPasteValues, SkipBlanks:=False, Transpose:=True


'Loop Through All Records
For I = 20 To 27
If (Range("A" & I) <> "") Then 'IsEmpty() 'function is for checking variables for initialization.
Range("A" & I & ":D" & I).Copy
Sheets("Data").Range("N" & NextRow).PasteSpecial Paste:=xlPasteValues
NextRow = NextRow + 1
End If
Next I
'Copy Data From Header Row Down


WSD.Range("A2:M2").Copy WSD.Range("A2:M" & NextRow - 1)
Application.CutCopyMode = False


' Store the name of the active worksheet


sWS = WSD.Name 'This somehow seems unnecessary, but that's just me.


' Copy Invoice to a new workbook with date/time locked to that date on the saved copy.


Sheets(Array(("Invoice"), ("Data"))).Copy
With ActiveWorkbook.Worksheets(sWS).Range("f7")
.Value = .Value
End With


NewFN = "C:\Users\Chris\Documents\Pawn Shop\Cash for Gold\Today Invoices\0806-1_" & Range("f6").Value & ".xlsx"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.BreakLink Name:="Cash for Gold.xlsm", Type:=xlExcelLinks ' added by JoeMo
ActiveWorkbook.Close
WSI.Range("F6").Value = WSI.Range("F6").Value + 1
WSI.Range("b6:b14,f7:f9,a20:e27").ClearContents
WSD.Range("2:10").ClearContents
For Each Pic In WSI.Pictures
lngTopRow = Pic.TopLeftCell.Row
If (lngTopRow > 31 And lngTopRow < 84) Then Pic.Delete
Next Pic


WSI.Activate


End Sub