CODEWITHSUNDEEP
vbaexcelloops
Bez
Bez

Reputation: 77

VBA macro that loops through changing sheet names

So we have an instrument that generates a bunch of data but names the sheets effectively randomly. I have the easy commands to go copy, paste and sort the respective text on the excel sheets but my problem is I can't figure out how to make the list of sheets that I generate loop through all those sheets while not specifying the name in the beginning..I don't want it to loop through all sheets because I need it to overlook the first sheet...

I'm getting an error 424 Object Req'd error. Any help would be greatly appreciated.

So I set all of my integers and variables

Dim x As Integer
Dim y As Integer
Dim a As Integer
Dim b As Integer
Dim compoundname As Range
Dim compoundtype As Range
Dim compoundrng As Range
x = 1
y = 3
a = 3
b = 2

y,a,b are all associated with my settings omitted from the last part.

So here I tell it that I want compound name to be the range only on the active sheet, which I think is my actual problem? 

Set compoundname = Workbook.ActiveSheet.Range("A3")

Set compoundrng = Sheets("AllSheets").Range("A3:A100")

And after I've added all the sheets to the workbook, I have the loop for the names that store on the "AllSheets" worksheet

For Each ws In Worksheets
    Sheets("AllSheets").Cells(x, 1) = ws.Name
    x = x + 1
Next ws

Then we have to tell it to access that list:

For Each compoundtype In compoundrng.Cells

copy, paste and sort my info here

Next compoundtype

Upvotes: 0

Views: 3089

Answers (1)

ClintB
ClintB

Reputation: 499

Why don't you do something like

For Each ws In Worksheets
    if(ws.Name <> 'YourFirstSheetName') Then
         'copy, paste and sort info here.
    end if
Next ws

Edit: Updated for your comment. If you don't care about cell formatting then don't use the copy/paste command. Just set the cell equal to the value of the other cell. That way you don't have to play around with clipboard or active sheets/cells, etc.

    Dim ws As Worksheet
    Dim x As Integer
    Dim y As Integer
    Dim a As Integer
    Dim b As Integer

    Set ws = Worksheets("Quant Sheet")
    y = 3

    Worksheets("Quant Sheet").Activate
    For Each ws In ActiveWorkbook.Worksheets
        If (ws.Name <> "Quant Sheet") Then
            Sheets("Quant Sheet").Cells(y, 1) = ws.Range("A3")
            y = y + 1
        End If
    Next ws

Upvotes: 1

Related Questions