G
Gordon
Hi...
I have 200 identical files that contain data that I need to populate a
standalone table. I have been using the following code superbly well to
extract pinpoint data from targeted sheets.
Sub MakeLinksForGordon()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub
However, the sheet name has now become variable as some of the files are no
longer identical. The above code now asks me which sheet I desire to search
when the expected sheet can't be found
What I need to happen is this:
In range b2:b200 I enter the path
In range c2:c200 I enter the file.xls
In cell c3 I enter the sheet name (sheet 1) that will apply to the code but
not the range, because not all of the files in the range will have sheet 1.
Basically what I enter in cell C3 will be the only sheet searched. If the
code comes across a file that doesn't have say sheet 1 then it skips it?
Sounds complex? It is...any help or alternative would be greatly appreciated.
Thanks
Gordon...
I have 200 identical files that contain data that I need to populate a
standalone table. I have been using the following code superbly well to
extract pinpoint data from targeted sheets.
Sub MakeLinksForGordon()
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = _
"='" & Cells(myCell.Row, 1).Value & "\[" & _
Cells(myCell.Row, 2) & "]" & _
Cells(myCell.Row, 3) & "'!" & _
Cells(1, myCell.Column)
Next myCell
End Sub
However, the sheet name has now become variable as some of the files are no
longer identical. The above code now asks me which sheet I desire to search
when the expected sheet can't be found
What I need to happen is this:
In range b2:b200 I enter the path
In range c2:c200 I enter the file.xls
In cell c3 I enter the sheet name (sheet 1) that will apply to the code but
not the range, because not all of the files in the range will have sheet 1.
Basically what I enter in cell C3 will be the only sheet searched. If the
code comes across a file that doesn't have say sheet 1 then it skips it?
Sounds complex? It is...any help or alternative would be greatly appreciated.
Thanks
Gordon...