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

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

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

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

Thanks James.

Sorry I've not been very clear.

I would like the VB not to define the active sheet as the workbook will be
used multiply times and saved with a different filename.

Taking your code below I would require the 'Sheet1' name to be a variable.
The user would rename this when the create a new file.

Hope this makes better sense and thank you for your patience.

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

Matthew,

I am a bit stumped as well. Are you attempting to set the active
workbook or activesheet in the workbook to a variable name? I assume
it is the workbook as you mentioned it would be saved with different
names.

Will the sheet (tab) always maintain the same postion in the workbook
(i.e. lead tab)?

Alan

Matthew Balch wrote:
Thanks James.

Sorry I've not been very clear.

I would like the VB not to define the active sheet as the workbook will be
used multiply times and saved with a different filename.

Taking your code below I would require the 'Sheet1' name to be a variable.
The user would rename this when the create a new file.

Hope this makes better sense and thank you for your patience.

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

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

I am sure we can get a solution for you. If your copy operation is
within the same workbook/template, can you ignore all file name
references and simply work within the active workbook?

I apologize and may need some further clarification. You refer to
"Year End Accounts - JANMIC.xls". Is this a sheet in the active
workbook you are activating or another workbook?

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

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

the workbook that contains the code can be referred to as ThisWorkbook.


the currently activeworkbook can be referred to as ActiveWorkbook

--
Regards,
Tom Ogilvy



"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

Thanks Alan.

Works. Hooray!

"Tom Ogilvy" wrote:

the workbook that contains the code can be referred to as ThisWorkbook.


the currently activeworkbook can be referred to as ActiveWorkbook

--
Regards,
Tom Ogilvy



"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