referencing workbook from cells in another workbook

Hi,

I'm trying to work out where I'm going wrong with the below but I've been stuck for a while and would appreciate any help.

I have a load of open workbooks to start of with, from where I am going to drag data. I then look to open the following workbook 'Names' which is a list of partial names of files ie cell a1 is MLUD*.xls

What I'm trying to do is select the first cell in the names spreadsheet "single", use this as a reference to activate an already open workbook with the name similar to MLUD*.xls and then copy data from this newly activated workbook (from the worksheet "Raw" to another file.

So far I've tried the below, but I'm obviously missing something in the syntax as it's not working
Any help would be really gratefully appreciated

thanks Joe

Sub MoveData ()

Dim WB As Workbook

Workbooks.Open ("S:\Names")
Worksheets("Single").Range("A1").Select

For Each WB In Application.Workbooks
If WB.Name Like ActiveCell.Value Then
WB.Activate

'Copy & Paste Data

Worksheets("Raw").Select
Range("b7:b10").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks("Macro Examples").Activate
Range("e7").Select
If ActiveCell.Value = "" Then
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Else: ActiveCell.Offset(0, 1).Select
End If




Exit For
End If
Next WB

End Sub

I think this is the source of the difficulty:
Workbooks.Open ("S:\Names")
Worksheets("Single").Range("A1").Select

the second line above is not looking in Names - since you do not specify
which workbook, it looks for the sheet Single in the workbook that contains
the code. Similarly with the copy/paste section of your code - you need to
specify the workbook if it is any workbook other than the one running the
code.

Activate and Select only work if you then use ActiveSheet, ActiveCell,
Selection, or the equivalent. But you don't worry about Activate and Select
as long as you specify the full reference to the sheet or range you will be
using (full reference is, e.g., Workbook().Worksheets().Range()....)

I think this may solve your problem:

Dim WBNames as Workbook, WB as Workbook
Set WBNames = Workbooks.Open ("S:\Names")
WBNames.Worksheets("Single").Range("A1").Select
....
' Copy and Paste Data
WB.Worksheets("Raw").Range("b7:b10").Copy
' To simplify I will use a With statement to refer to the cell you want to
paste to
' NOTE: for the line below would need to specify the sheet name, which I
don't know
With Workbooks("Macro Examples").Worksheets(?).Range("e7")
If .Value = "" Then
PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Else
.Offset(0,1).Select
End If
End With
....
--
- K Dales


"jlejehan" wrote:

>
> Hi,
>
> I'm trying to work out where I'm going wrong with the below but I've
> been stuck for a while and would appreciate any help.
>
> I have a load of open workbooks to start of with, from where I am going
> to drag data. I then look to open the following workbook 'Names' which
> is a list of partial names of files ie cell a1 is MLUD*.xls
>
> What I'm trying to do is select the first cell in the names spreadsheet
> "single", use this as a reference to activate an already open workbook
> with the name similar to MLUD*.xls and then copy data from this newly
> activated workbook (from the worksheet "Raw" to another file.
>
> So far I've tried the below, but I'm obviously missing something in
> the syntax as it's not working
> Any help would be really gratefully appreciated
>
> thanks Joe
>
> Sub MoveData ()
>
> Dim WB As Workbook
>
> Workbooks.Open ("S:\Names")
> Worksheets("Single").Range("A1").Select
>
> For Each WB In Application.Workbooks
> If WB.Name Like ActiveCell.Value Then
> WB.Activate
>
> 'Copy & Paste Data
>
> Worksheets("Raw").Select
> Range("b7:b10").Select
> Application.CutCopyMode = False
> Selection.Copy
> Workbooks("Macro Examples").Activate
> Range("e7").Select
> If ActiveCell.Value = "" Then
> Selection.PasteSpecial Paste:=xlPasteValues,
> Operation:=xlNone, SkipBlanks _
> :=False, Transpose:=False
> Else: ActiveCell.Offset(0, 1).Select
> End If
>
>
>
>
> Exit For
> End If
> Next WB
>
> End Sub
>
>
> --
> jlejehan
> ------------------------------------------------------------------------
> jlejehan's Profile: http://www.excelforum.com/member.php...o&userid=33950
> View this thread: http://www.excelforum.com/showthread...hreadid=538331
>
>

Thanks - that makes sense to an extent and solves a part of the problem. My major problem, though, is still activating the exact workbook from the 100 or so that are open.

When I open the "S:\names" file this has a list of partial names in cells a1 down to a100

for instance it may, in cell a1, say "mlud" - This would be the potential start of a file name such as "mlud.xls" that is open - but may not be the active workbook at the moment. So I'm trying to reference the fact that mlud relates to "mlud.xls" - but for some reason it doesn't seem to pick up that fact when I use

If WB.Name Like ActiveCell.Value then
WB.Activate

where the 'ActiveCell.Value' for the cell a1 in the workbook names would be "mlud"

I really appreciate the help already given and any further assistance in trying to work out where I'm going wrong with the above

thanks. joe.