Loop through Sheets

[elmnas]

Hello people,


I need a script that loop through all sheets in a open workbook?

if a column first cell (always A1) have value="ordernr"
then
select the whole column


Could someone help me?

I wrote this could but doesn't work

Sub GetData()



Dim s As String
Dim Current As Worksheet
For Each Current In Worksheets
For Each C In ActiveSheet.UsedRange
If C.Value = "Ordernr" Then


Columns(1).Select
'Selection.Copy




'Workbooks("test1").Activate




'ActiveSheet.Name = "Company"




End If
Next
Next






End Sub

Thank you in advance

 

[SteveO59L]

if a column first cell (always A1) have value="ordernr"
then
select the whole column

and do what with the selected column?

 

[steve the fish]

This loops through the sheets but you don't say what to do so in this case it just copies column A where the value is found:

Sub LoopThroughWorkbook()

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
    If sh.Range("A1").Value = "Ordernr" Then
        sh.Range("A:A").Copy
    Exit For
    End If
Next sh

End Sub

 

[elmnas]

This loops through the sheets but you don't say what to do so in this case it just copies column A where the value is found:

Sub LoopThroughWorkbook()

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
    If sh.Range("A1").Value = "Ordernr" Then
        sh.Range("A:A").Copy
    Exit For
    End If
Next sh

End Sub

the code doesnt work for me, it still on the same sheet in the workbook hmm?

 

[steve the fish]

Is the correct column copied?

 

[elmnas]

Is the correct column copied?

Yes it is hmm

 

[Joe4]

The way you have written your code, I believe you may actually need to select the sheet first, i.e.

For Each sh In ActiveWorkbook.Worksheets
    If sh.Range("A1").Value = "Ordernr" Then
        sh.Activate
        sh.Range("A:A").Copy
        Exit For
    End If
Next sh