Loop through worksheets 1

[micang]

Excel 2007

Hi All,

I am trying to figure out how to loop through all worksheets in an open excel workbook.

I am trying something basic at first and after many attempts from samples on the net, I just can't figure out why it's not working for me.

Here is a basic attempt:

Sub mloop()
   Dim wSheet As Worksheet
   For Each wSheet In Worksheets
    Range("A1").Value = 1
    Next wSheet
End Sub

I do not understand why it's not looping. It only put's in the value (1) in the active sheet, but not the others in the workbook.

Any assistance will be greatly appreciated.

Michael

 

[SkipVought]

Hi,

Reference the worksheet object...

Sub mloop()
   Dim wSheet As Worksheet
   For Each wSheet In Worksheets[b]
      wSheet.[/b]Range("A1").Value = 1
   Next wSheet
End Sub

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[micang]

Hi Skip,

Thank you, that does work.

I am trying now using that as a starting point and am trying to select a range in each sheet and changing the cell types to "General":

Sub mloop()
   Dim wSheet As Worksheet
   For Each wSheet In Worksheets
  wSheet.Range("A1:AA100").NumberFormat = "General"
   Next wSheet
End Sub

The above does not work, but also gives no error.

I can see that I am not really selecting a range I think.

I appreciate your time and input.

Michael

 

[SkipVought]

"The above does not work..."

Why do you say that? It certainly DOES work!



Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[micang]

Skip, your're right!

Many thanks for your help.

Michael

 

[krispi]

Hi all - could someone give me a hint as to why this test code is falling over? The idea is to do lookups based on column a, rows 1,2 and 3 of wb1, on columns A and B of each worksheet in wb2, then place the results in columns B, C and D of wb1.

Just trying to get the logic to work at this stage so I can apply it to some real data.

Code is below, and on debugging through the Watch window, it gives me a run time error 438 at: 'For Each ws In wb2'

Any help would be gratefully appreciated!

Private Sub test1()

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim intR As Integer
Dim intC As Integer
Dim LURange As Range
Dim LUValue As String
Dim strVal As String
Dim myVar As Variant

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("J:\Chris\Automation\Test1.xls")
intC = 2
For Each ws In wb2
     Set LURange = ws.Range("A:B")
        intR = 1
            Do While intR < 4
                LUValue = wb1.Worksheets("Sheet1").Cells(intR, 1)
                myVar = Application.VLookup(LUValue, LURange, 2, False)
                wb1.Worksheets("sheet1").Cells(intR, intC).Value = myVar
                intR = intR + 1
            Loop
        intC = intC + 1
    Next ws
End Sub

 

[krispi]

sorry - meant to post the above in a new post, not a reply - apologies!!!!

 

[PHV]

Anyway, replace this:
For Each ws In wb2
with this:
For Each ws In wb2.Worksheets

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Newbie2223]

Hi All

I found this piece of code that works on the Active Sheet
The problem I have is that my excel workbook was create by someone else
and populates over 5 sheets..once I open the workbook.

I would like to build a macro to auto copy sheet 3, 4 and 5 only
How or what do I have to add to get the below code to
loop thru sheet 3, 4 and 5


Sub SaveAsPipeDelimited()
Dim r, c As Integer
Close
Open "C:\Just_Text.txt" For Append As #1
With ActiveSheet.UsedRange
For r = 1 To .Rows.Count
For c = 1 To .Columns.Count - 1
Print #1, .Cells(r, c); "|";
Next c
Print #1, .Cells(r, .Columns.Count)
Next r
End With
Close #1
End Sub

Any help is greatly appreciated

Thanks Newbie2223