Cycle through data in columns and rows 1

[binway]

Hello,
I have data in multiple columns and rows in a spreadsheet. I want to start at cell A1, read the value, perform some functions then move to A2 and so on until all the data in that is read I then need to go to the next column so it would be cell B1.
Some sample data is below:

170A 150B 220C
111J 243G 235G
554M 123G 456G
123H 567J
564N

I can cycle through reading the data in a column easily enough using the code below but I can't work out how to make it move back to row 1 and then to the next column.

With Sheets("Sheet1")
i = TotCols
'This line counts the rows that have data in the column
'assumption is that the data will always land in the same column
LR = .Range("A" & Rows.Count).End(xlUp).Row
For Each cell In .Range("A1:A" & LR)
If cell.Value <> "" Then
'Sheets("Sheet2").Copy After:=Sheets(2)
Sheets.Add(After:=Sheets(Sheets.Count)).Name = cell
Range("B2") = cell
ActiveWorkbook.SaveCopyAs Filename:="C:\Temp\" & cell & Dt & ".xls"
End If
Next cell
'Cells(Selection.Row, Columns.Count).End(xlToLeft).Offset(, 1).Select
End With


Any help would be appreciated

 

[SkipVought]

hi,

Sub sample()
    Dim r As Range, c As Range, dt As String
    
    With Sheets("Sheet1")
        For Each c In .Range(.Cells(1, "A"), .Cells(1, "A").End(xlToRight))
        '[b]c starts with A1 and extends to the right until an empty cell[/b]
            For Each r In .Range(c, c.End(xlDown))
            '[b]r starts with c and extends down until an empty cell
                'do your stuff here with r as the cell range object[/b]
                Sheets.Add(After:=Sheets(Sheets.Count)).Name = r.Value
            '[b]do you REALLY want to write and OVERWRITE each r value into B2?????[/b]
                Range("B2") = r.Value
                ActiveWorkbook.SaveCopyAs Filename:="C:\Temp\" & r.Value & dt & ".xls"
            Next
        Next
    End With
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[binway]

Thanks Very Much Skip.
Works like a charm.
I have bit more to do in the "do your stuff here" section like saving the file naming it for each of the values in row 1 eg 170A.xls but I will try and work through this myself - all part of the learning experience.
Nice of you to clarify the populating of the r value in B2, it won't overwrite this value as it creates a new sheet and then puts the r value in B2 of the new sheet.

Thanks Once again.

 

[Andrzejek]

Looks like Skip's post was helpfull - use
[blue]
Like this post?
Star it![/blue]

in his post to show appreciation

Have fun.

---- Andy