Excel VBA : Sheet selection

[vlbaranov]

Hi all,
I have a spreadsheet out of which (using a VBA macro) I extract dat
into a comma-delimited text file. below is the main loop construct t
get each cell within each row.

For Each myRecord In Range("A1:A" & Range("A"
Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
<<<processing : pos(cell) = myField.Text 'etc>>>
Next Myfield
End With
Next MyRecord
The problem I am having is that the macro will work on the Activ
(open) sheet when I need it to always use a certain sheet.
Is there a simple solution for that?

Another issue I am having is that there are column filters set up
Sometimes the macro will not dump all opf the records (it would leav
out last 15 or so rows). I am wondering if there is a way to "disable
the filtering in the code of the macro (with the filter "on" th
results are intermittent, with all filters off the results ar
consistent. The picture attached has an example snapshot of the colum
filter.
Thank in advance,
Vitali Barano

Attachment filename: filters.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=56192

 

[Frank Kabel]

Hi
something like

'....
Dim wks as worksheet
set wks = activeworkbook.worksheets("special_sheet")
with wks
For Each myRecord In .Range("A1:A" & .Range("A" &
..Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
<<<processing : pos(cell) = myField.Text 'etc>>>
Next Myfield
End With
Next MyRecord
next with

 

[Tom Ogilvy]

Dim myRecord as Range
Dim myFiled as Range
For Each myRecord In Worksheets("Sheet1") _
.Range("A1").CurrentRegion.Rows

For Each myField In MyRecord.Cells
<<<processing : pos(cell) = myField.Text 'etc>>>
Next Myfield

Next MyRecord

 

[vlbaranov]

That worked as a charm .. the macro just pics the Sheet I nee
[B.I.N.G.O]
I still get intermittent results if I have column filters set t
anything other than "All" (see pic attached in the original post)
Still wondering how to make sure macro can see ALL of the row
regardless of filters, etc...
Thanks for help, this is the best forum I have ever been on ... th
only one I have ever been a member of ;] Kudos to all responsibl

 

[Tom Ogilvy]

My answer would solve that, but . . .

 

[Earl Kiosterud]

VL,

A couple of comments about working in these newsgroups. You should post
your reply to Tom's reply as a reply to his, not to your original post.
That keeps the threading more easy to follow for all if there are many
replies. Also, attachments are stripped.

Hope this helps.

 

[vlbaranov]

My appologoies Earl, I was just using the "Post Reply" and the only on
I see is at the end of the thread so thats the one I have used. I wa
actually trying to reply to the post just before Tom's and not too sur
how it got to be after Tom's.
As per Tom's suggestion, I have tried it and don't get get any record
exported, stepped though the code and it seems it does not iterat
rather goes through the loop only once. I will keep trying (especiall
hard with no help .. support yet to install) but in the meanwhile
thank you all for the help and my appologies to the moderators for an
incorrect use of this forum

 

[Tom Ogilvy]

Must be that you are not using the correct objects. With an autofilter
applied, it still picked up every cell.

Sub Tester5()
Dim myRecord As Range
Dim myFiled As Range
For Each myRecord In Worksheets("Sheet1") _
.Range("A1").CurrentRegion.Rows

For Each myfield In myRecord.Cells
Debug.Print myfield.Address
Next myfield

Next myRecord

End Sub

Produced (data in A2:C5)
$A$1
$B$1
$C$1
$A$2
$B$2
$C$2
$A$3
$B$3
$C$3
$A$4
$B$4
$C$4
$A$5
$B$5
$C$5

if you don't want to process the first row:

Sub Tester5()
Dim myRecord As Range
Dim myFiled As Range
Dim rng as Range
set rng = Worksheets("Sheet1") _
.Range("A1").CurrentRegion.Rows
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
For Each myRecord In rng
For Each myfield In myRecord.Cells
Debug.Print myfield.Address
Next myfield

Next myRecord

End Sub

 

[vlbaranov]

Thanks a ton Tom, I will try the construct on another "tester" simila
to what you had in example above, I must be overlooking something

 

[vlbaranov]

Tom,
it all worked like a charm, it just did not like Worksheets("Sheet1"
but rather wanted to see Worksheets(1). It stopped acting up as soon a
I changed that.
Once again, thank you all for your help, I really apreciate it