Excel VBA looping through multiple worksheets

by

You should be declaring rng for each ws inside the loop, like:

Sub exercise()
    Dim ws As Worksheet
    Dim rng As Range

    For Each ws In ActiveWorkbook.Worksheets
        'Finding last row in column B
        Set rng = ws.Range("B" & ws.Rows.Count).End(xlUp) '<~~ Moved inside the loop
        'Don't copy data from xws worksheet
        If ws.Name <> "xws" Then
            'Storing first copied data in A1
            If IsEmpty(Sheets("xws").[A1]) Then
                rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp)
            'Storing next copied data below previously filled cell
            Else
                rng.Copy Sheets("xws").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            End If
        End If
    Next ws
End Sub

As your code is now, rng will be pointing to the ActiveSheet at the time you run the macro, and your code will then copy the same cell on each iteration of the code.

More Related Contents:

  1. to make this code simpler
  2. VBA code takes long time to execute in excel
  3. Excel column number from column name
  4. Excel VBA deleting rows in a for loop misses rows
  5. Execute a command in command prompt using excel VBA
  6. What is the lifetime of a global variable in excel vba?
  7. Download attachment from Outlook and Open in Excel
  8. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  9. Embed picture in outlook mail body excel vba
  10. How to check for empty array in vba macro [duplicate]
  11. Use autofilter on more than 2 criteria
  12. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  13. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  14. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  15. Load csv file into a VBA array rather than Excel Sheet
  16. Insert line break in wrapped cell via code
  17. Change color of certain characters in a cell
  18. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  19. Making VBA Form TextBox accept Numbers only (including +, – and .)
  20. Consolidating worksheets into one
  21. VBA Macro crashes after 32000 rows
  22. How to create dynamic variable names VBA
  23. How to break a long string into multiple lines
  24. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  25. simple vba code gives me run time error 91 object variable or with block not set
  26. How do I open a file if I only know part of the file name?
  27. VBA recognizing workbook by partial name
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment