Copy titles from another workbook

I

Indu Aronson

There are several worksheets that I use that have the same
titles. I have created a file at C:\ILA\GuestTitles.xls
that have the titles I want in Sheet1 of that workbook.

How can I copy the titles in GuestTitles.xls to any
current worksheet.

I have this much:
Workbooks.open Filename:="C:\ILA\GuestTitles.xls"

I can't predict the name of the activeworksheet (the
target sheet) and thus am not sure how to copy the titles
over.

GuestTitles has titles from A1:L2.

I hope I have been clear enough for a response.

Thank you.
 
A

Anders S

Hi Indu,

Here's one try:

----

Option Explicit

Sub copyTitles()
Dim destSheet As Worksheet
Dim srcBookName As String, srcBook As Workbook
Set destSheet = ActiveSheet
srcBookName = "C:\ILA\GuestTitles.xls"
Workbooks.Open Filename:=srcBookName
Set srcBook = ActiveWorkbook
ActiveSheet.Rows(1).Copy Destination:=destSheet.Rows(1)
srcBook.Close
End Sub
 
G

Gord Dibben

Indu

For NEW worksheets without the VBA and copying.......

Create a new workbook with one sheet.

On that sheet place your titles and any other formatting you wish.

File>Save As>File Type Template(*.xlt)

Name it SHEET and save to your XLSTART folder.

Any new inserted worksheet will be based on SHEET.xlt with your titles
included.

Gord Dibben Excel MVP
 
I

Indu

Thanks Anders and Gord for your help.

(Gord, I couldn't use a template because I already had
data in various sheets and changed my mind about the
titles!)

-----Original Message-----
Hi Indu,

Here's one try:

----

Option Explicit

Sub copyTitles()
Dim destSheet As Worksheet
Dim srcBookName As String, srcBook As Workbook
Set destSheet = ActiveSheet
srcBookName = "C:\ILA\GuestTitles.xls"
Workbooks.Open Filename:=srcBookName
Set srcBook = ActiveWorkbook
ActiveSheet.Rows(1).Copy Destination:=destSheet.Rows (1)
srcBook.Close
End Sub

----

HTH
Anders Silven

"Indu Aronson" <[email protected]>
Click to expand...
skrev i meddelandet [email protected]...
 
Top