Loop???

[jhahes]

I am stuck trying to do the following. Can someone please help. I am
novice and have Excel 2003.

I am in Sheet10.

I want the code to look at cell C2 in Sheet10.
If cell C2 in Sheet10 is blank, then go to C3 in Sheet10. (offset 1,0)

The range can go down to cell C23


If a cell in the range has a value then I want it to open a sheet ( th
name of the sheet is in the corresponding Column A of Sheet10.)

For example

The code looks starts in Sheet10 (Column C, Row2) finds that it i
empty and goes to the next row in that column. Finally in Row 10 i
finds a value. So then I want it to go to the corresponding ro
(Column A) or activecell.offset(0,-2) and open that sheet. So in thi
example, say Column A, Row 10 value is (Fiesta). So I want the code t
open Sheet45(Fiesta) and put the value in column c row 10 in Fiest
sheet "A1"), then print Fiesta Sheet. And finally loop thru rows 3:2
in Sheet10.

Sorry, this probably wasn't explained very well.

I would appreciate any help.

thanks Jos

 

[Tom Ogilvy]

Dim cell as Range, bk as Workbook
for each cell in Range("C2:C23")
if not isempty(cell) then
set bk = workbooks.Open("C:\Myfolder\" & cell.offset(0,-2).Value _
& ".xls)
bk.Worksheets("Sheet45").Range("A1").Value = cell
bk.Worksheets("Sheet45").Printout
bk.close Savechanges:=False
End if
Next

Your description falls apart where you say what you want to do after opening
the workbook, so adjust the macro to suit your needs.

 

[Guest]

So if I am understanding you correcntly, you want each row to correspond to a
different sheet? So C3 = sheet 1, C4 = sheet 2? and so on? If thats the case
try this...

Dim strSheet As String
Dim strCell As String
Dim strCellAddress As String

Sheets("Sheet1").Select
Range("C3:C23").Select

For Each cell In Selection
Sheets("Sheet1").Select
If ActiveCell = strCell Then
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell = "" Then
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell <> "" Then
strCell = ActiveCell
strCellAddress = Selection.Columns.Address
ActiveCell.Copy
strSheet = ActiveCell.Offset(0, -2)
Sheets(strSheet).Select
Range(strCellAddress).Select
ActiveSheet.Paste
Workbook.Worksheets(strSheet).PrintOut
End If
If Selection.Columns.Address = "$C$23" Then
Exit For
End If

Next cell

Sheet 1 would be whatever sheet it is you are getting your data from. Here
is what you do, find an empy column, and place the sheet name in the
corresponding row, this is where the "strSheet = ActiveCell.Offset(0, -2)"
comes into play. So if your sheet name is in column d you would do
offset(0,1) instead.