2

I'm a newbie. I have an excel file with 10 sheets, 6 sheets named after 6 employee names the next 3 with some information (irrelevant to my code) and the 10th sheet named Temp.

The employee sheets have the following data in each column (D&E are blank):

| A         | B                | C | D | E | F                          |
| 17-Sep-13 | ProjectA         | 6 |   |   | Report updated on this day |
| 18-Sep-13 | CBL Ideas - HMF  | 7 |   |   |                            |
| 18-Sep-13 | CBL Ideas - HMF  | 1 |   |   |                            |

I want to have all these data collated in the sheet named Temp as follows:

| A         | B         | C | D   |
| 17-Sep-13 | Project A | 6 | foo |
| 18-Sep-13 | Project A | 7 | foo |
| 18-Sep-13 | Project B | 1 | foo |
| 17-Sep-13 | Project A | 6 | bar |
| 18-Sep-13 | Project A | 7 | bar |
| 18-Sep-13 | Project B | 1 | bar |

Below is my code:

Sub ListRelevantEntries()
  Dim s As Integer
  Dim C As Range

  For s = 1 To Worksheets.Count - 4  
    If Sheets(s).Cells(Rows.Count, "F").End(xlUp) _
          .Value = "Report last updated on this day" Then
      'Execution stops on the below line with an
      ' "Application-defined or object defined error"
      Sheets(s).Range(Cells(Rows.Count, "F").End(xlUp) _
          .Offset(1, 0), Cells(Rows.Count, _ "A").End(xlUp)) _
          .Copy(Sheets("Temp").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0))

      Sheets("Temp").Select
      Sheets("Temp").Cells(Rows.Count, "C").End(xlUp).Offset(0, 1).Select
      For Each C In Sheets("Temp").Range(Cells(Rows.Count,"C").End(xlUp). _
        Offset(0, 1), Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)).Cells
        C = Sheets(s).Name
      Next
    ElseIf Not Sheets(s).Cells(Rows.Count, "F").End(xlUp) _
          .Value = "Report last updated on this day" _
          And Not Sheets(s).Cells(Rows.Count, "F").End(xlUp).Value = "" Then
  MsgBox "Extra Words entered " & ActiveSheet.Cells(Rows.Count, "F") _
      .End(xlUp).Offset(1, 0).Value & " in " & Sheets(s).Name
    End If
  Next

  Sheets("Temp").Range("1:1").Delete  
End Sub

Sorry for such a long question. I couldn't think of any other way to explain!

Community
  • 1
  • 1
Srmsbrmnm
  • 149
  • 4

3 Answers3

2

The error will be a lot easier if you clean up your code. Add a worksheet variable at the start

Dim ws As Worksheet

Then after the first For statement assign the target sheet to it

  Set ws = Worksheets(s)

Notice I used Worksheets(s) not Sheets(s). They are different, don't mix them. The Sheets collection can include Charts as well as Worksheets, it represents every tab. The Worksheets collection only contains Worksheet objects. In your For loop you used Worksheets, then you used Sheets within the loop. This will break if any charts are in the workbook.

Okay, so now that you have this ws variable containing a reference to the worksheet, go ahead and replace all your Sheets(s) with ws inside the for-loop's body. While you are at it, fix all your calls to Cells and Rows. Anywhere you write something like ws.Range(Cells(1, "F")) you are making a mistake. Cells alone points to ActiveSheet.Cells where as you want ws.Cells. Otherwise you are trying to create a cross-worksheet range any time ws is not ActiveSheet. The same goes for other properties of a range, such as Rows. So now the line of code you're stopping on should be more like this:

ws.Range(ws.Cells(ws.Rows.Count, "F").End(xlUp) _
      .Offset(1, 0), ws.Cells(ws.Rows.Count, "A").End(xlUp)) _
      .Copy Sheets("Temp").Cells(Sheets("Temp").Rows.Count, "A") _
      .End(xlUp).Offset(1, 0)

Notice, I also removed the parentheses around the parameter you're passing to .Copy. In VBA you call a method without parentheses unless it returns a value. So MyMethod "Value to pass" or result = MyMethod("Value to pass") but not MyMethod("Value to pass").

After fixing all that, if it still is producing the error, I'd recommend using the debugger. Open the watch window and start breaking the offending line into bits, examine them, and find the problem.

So first you might create a watch for ws and make sure it's the correct sheet. Then maybe edit that watch to be ws.Cells(ws.Rows.Count, "F").End(xlUp) and see if that is getting the correct cell.

Oh, also, if you're doing it properly you shouldn't need to call anything like Worksheet.Select or .Activate. In fact, it's recommended you avoid those the vast majority of the time, unless you are interacting with the user by changing their view.

AndASM
  • 9,458
  • 1
  • 21
  • 33
  • @AndASM Vow that was an amazing answer didn't just clear my doubt and make my code work, but also taught me a great deal. Thank you so much. I hope I'd be able to help people like this when I become reasonably knowledgeable :) Thank you once again.. – Srmsbrmnm Nov 13 '13 at 18:55
0

Replace with something like

With Sheets(s)
        .Range(.Cells(.Rows.Count, "F").End(xlUp).Offset(1, 0), .Cells(.Rows.Count, "A").End(xlUp)).Copy Sheets("Temp").Cells(Sheets("Temp").Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
sam092
  • 1,325
  • 1
  • 8
  • 8
  • Thank you for the answer. My code worked with AndASM's answer above. However, your answer helped me note the use of With Statement. Thanks. – Srmsbrmnm Nov 13 '13 at 18:57
0

you get the error since you did not return the reference to the sheet(s). A quick fix is to add this:

Sheets(s).Select

prior line 6 or after line 4.

But for better coding, try using whats seen in This Thread.
That link discusses how you can avoid using select by declaring and setting all your objects.

Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68