Matthew,
I modified the code you posted. Try this and see if it works. Post back
if more help is needed.
Alan
Dim WBName As String
WBName = ActiveWorkbook.Name
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(WBName).Activate
Range("A2").Select
ActiveSheet.Paste
Matthew Balch wrote:
Hi Alan,
It is the name of the workbook which will be variable.
The 'Sheet1' will always remain the lead sheet of the workbook.
To reiterate:-
I have a template which will be used for numerous clients upon which the
filename will be changed accordingly. Within my current macros I have used
the filename reference to perform tasks. When a user creates a new workbook
and changes the filename the macros then become out of date (dont work).
(Unless you change the macro itself manually - which I would like to avoid
the user doing!)
Thanks for your help
Matthew
"Zone" wrote:
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