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