About Us

Matthew Balch[_2_]

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Calculate active sheet on sheet selection	a94andwi[_11_]	Excel Programming	1	September 26th 05 05:23 PM
Selection from list on main sheet from suplemental sheet in same w	Kelly	New Users to Excel	1	August 12th 05 04:37 PM
Action on sheet Selection	jpizzle[_8_]	Excel Programming	4	June 13th 05 05:33 PM
Sheet selection	No Name	Excel Programming	1	October 6th 04 05:02 PM
Inserting a row in sheet A should Insert a row in sheet B, removing a row in Sheet A should remove the corresponding row in sheet B	Hannes Heckner	Excel Programming	1	March 5th 04 09:10 AM

Menu

About Us