referencing workbook from cells in another workbook

[jlejehan]

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

 

[Guest]

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
....

 

[jlejehan]

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

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

for instance it may, in cell a1, say "mlud" - This would be th
potential start of a file name such as "mlud.xls" that is open - bu
may not be the active workbook at the moment. So I'm trying t
reference the fact that mlud relates to "mlud.xls" - but for som
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 name
would be "mlud"

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

thanks. joe