Simple VBA Script Error

Question

I'm still pretty new with VBA (learning for work, coming from a JS background) and I need a little help with this. My goal is: loop through each worksheet (except the "Summary" sheet, although I'm not sure how to exclude this from the loop) in the workbook and copy A2 in each sheet, as well as the last cell containing a value in column L of each sheet, and paste those next to each other in columns A and B on the "Summary Sheet", respectively. I'm not an expert with VBA syntax by any means, so if anyone has any way to refactor this (I know I don't need all the .select methods), I would appreciate it. Right now I'm getting an "invalid or unqualified reference" error on line 28. My goal is to learn, so if you have any input I would appreciate a short explanation of the logic. Thanks.

            Sub Macro7()
            '
            ' Macro7 Macro
            '
            ' Keyboard Shortcut: Ctrl+c


            Dim ws As Worksheet
            Dim lastRow As Integer
            Dim summaryRow As Integer

                summaryRow = 1

            For Each ws In ActiveWorkbook.Worksheets

            'Copy item number and paste on Summary Page'

                Range("A2").Select
                Selection.Copy
                Sheets("Summary").Select
                Range("A" & summaryRow).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

            'Copy corresponding BOM item # and paste on Summary Page'

                ws.Select
                lastRow = .Cells(.Rows.Count, "L").End(xlUp).Row
                Range("L" & lastRow).Select
                Application.CutCopyMode = False
                Selection.Copy
                Sheets("Summary").Select
                Range("B" & summaryRow).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                summaryRow = summaryRow + 1

                    Next ws
            End Sub

You are prefacing `.Cells(...` and `.Rows...` with a period (e.g. `.` or *full stop*). This manner of cell referencing is intended to be used within a [With ... End With statement](https://msdn.microsoft.com/en-us/library/wc500chb.aspx). Start by remving the period or adding a With ... End With. — , Jun 01 '16 at 20:37
@Jeeped thanks, that got it to run without any errors. However, it gave some obscure results. It pasted the values in column B, but nothing in column A (on the Summary Sheet). Additionally, it skipped some random rows here and there; any suggestions? FWIW, there are over 300 sheets in this ridiculous report. — trevor_bye, Jun 01 '16 at 20:44

score 2 · Answer 1 · edited May 23 '17 at 10:28

You can avoid using .Select¹ by constructing a With ... End With statement that passes the parent worksheet reference along to the cells and range references. Direct value transfer is more expedient and more efficient than copying and pasting. Additionally, it avoids involving the clipboard altogether.

Sub Macro7()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim summaryRow As Long

    summaryRow = 1

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If LCase(.Name) <> "summary" Then
                Worksheets("Summary").Range("A" & summaryRow).Resize(1, 2) = _
                    Array(.Range("A2").Value, .Cells(Rows.Count, "L").End(xlUp).Value)
                summaryRow = summaryRow + 1
            End If
        End With
    Next ws

End Sub

.¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

thanks for your help, your explanation makes perfect sense. I was trying to figure out the purpose of the With..EndWith.. and that makes sense that attaches that parent reference to the children. I was able to get it working by adding ws.Select above the first block of the loop, but I'll work on refactoring it to get it to run faster and look more professional. Thanks! — trevor_bye, Jun 01 '16 at 20:52

score 1 · Answer 2 · answered Jun 01 '16 at 20:53

Your code looks fine apart from the '.' reference Jeeped pointed out.

I have just neatened up your code here as using .select is a little messy and long-winded (if you are building big macros it can add unnecessary work and slow it down a lot).

You can just do the commands straight from a range reference as shown below:

     Sub Macro7()

        Dim ws As Worksheet
        Dim lastRow As Integer
        Dim summaryRow As Integer

            summaryRow = 1

        For Each ws In ActiveWorkbook.Worksheets

        'Copy item number and paste on Summary Page'

            Range("A2").Copy
            Sheets("Summary").Range("A" & summaryRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

        'Copy corresponding BOM item # and paste on Summary Page'

            lastRow = ws.Cells(Rows.Count, "L").End(xlUp).Row
            Application.CutCopyMode = False
            ws.Range("L" & lastRow).copy
            Sheets("Summary").Range("B" & summaryRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False

            summaryRow = summaryRow + 1

                Next ws
        End Sub

I typically code out worksheets("").Range("").action everytime unless I am doing a lot of work on 1 sheet so that I can see easily what is going on when I glance over, but you'll find your own preference to working (don't forget you can step through your code with F8 to look at what it is doing every step of the way)

I hope this helps you start your VBA journey!

Simple VBA Script Error

2 Answers2