I am trying to write a code that will look through all available worksheets in a workbook and return the data. Currently my code is written to look for specific sheets and it creates an error when a worksheet is added or deleted. I would like a code that will start at one sheet and search all remaining sheets in the workbook.

My current code is:

Sheet7.Select
Range("A28:M68").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheet8.Select
Range("A28:M68").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("A40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


The other problem I might run into is my range needs to change each time a new sheet is found. The first one says Range("A1").Select, the second Range("A40").Select, the third would say Range("A80").Select, and so on....

Please help :) ,
OBS

Below the code for a loop through the worksheets.
Also a piece of pseudo coding for the change in ranges.
Add your code inside the loop...

Sub Test
    Dim ws As Worksheet
    Dim iCount as Integer
    For Each ws In ActiveWorkbook.Sheets
      iCount = iCount + 1
      '--add your code here---
           'Range("A" & Cstr(iCount*40)).Select
           MsgBox  "A" & Cstr(iCount*40)
      '--add your code here---
    Next
End Test
This article has been dead for over six months. Start a new discussion instead.