Matthew, Best I can do is show an example. This code copies a range
from the worksheet named "Sheet1" to the worksheet named "Sheet2" in
the same workbook (the activeworkbook) without selecting either of the
sheets. Hope this helps. James

Sub CopyToOther()
Dim mySht As String
mySht = "Sheet2"
Worksheets("Sheet1").Range("a1:a15").Copy
Destination:=Worksheets(mySht).Range("a1")
End Sub

Matthew Balch wrote:
Thanks Zone.

Unfortunately doesn't solve my problem and what I though would with the fime
name being in A1 wouldn't either.

Therefore, how can I change the following code so that it doesnt select the
other sheet:-

Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Windows("Worksheet in Basis (1)").Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Year End Accounts - JANMIC.xls").Activate
Range("A2").Select
ActiveSheet.Paste

The Year End Accounts - JANMIC is my activesheet. The Worksheet in Basis is
the one I want to get the relevant data from.

"Zone" wrote:

Matthew, I'm not sure if I quite understand your question. But say you
want to save the name of the workbook in cell A1 of Sheet1 of the
workbook with the code in it. Then, save it with
ThisWorkbook.Worksheets("Sheet1").[A1]="MyWorkbookName.xls"
or
ThisWorkbook.Worksheets("Sheet1").[A1]=Activeworkbook.Name

Then retrieve it with something like
Dim myWkbk as String
myWkbk=ThisWorkbook.Worksheets("Sheet1").[A1]
James

Matthew Balch wrote:
Hi,

How do I select a sheet without using the sheet name?

This is the macro I have at present:-

Sub ImportCosts()
'
' ImportCosts Macro
' Macro recorded 16/10/2006 by Administrator
'

'
Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Windows("Worksheet in Basis (1)").Activate
Range("A2:J2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Year End Accounts - JANMIC.xls").Activate
Range("A2").Select
ActiveSheet.Paste


As this spreadsheet will be used over and over how do I get the spreadsheet
name to change with it in the macro.
The variable bit of the above being: "Year End Accounts - JANMIC"

I presume I would need a bit of VB that would write the fle name somewhere,
then use this as my reference for the above? If so, how do I do this?

Thanks in advance
Matthew Balch