A Sheet by any other name.....
 

Hi All........
I am opening a second Excel file and copying the only sheet therein back to
my original file. All code works fine, except that it requires me to
hardcode the SheetName of the second file. I would like to be able to
retrieve the sheet regardless of what it might be named.

Here's what I'm using now

Sheets("BUYER_CB").Select
Sheets("BUYER_CB").Copy After:=ThisWorkbook.Sheets(1)


TIA for any assistance
Vaya con Dios,
Chuck, CABGx3

A Sheet by any other name.....
 

One way:

Sheets(1).Copy After:=ThisWorkbook.Sheets(1)

Note that the first line isn't required.


In article ,
CLR wrote:

Hi All........
I am opening a second Excel file and copying the only sheet therein back to
my original file. All code works fine, except that it requires me to
hardcode the SheetName of the second file. I would like to be able to
retrieve the sheet regardless of what it might be named.

Here's what I'm using now

Sheets("BUYER_CB").Select
Sheets("BUYER_CB").Copy After:=ThisWorkbook.Sheets(1)


TIA for any assistance
Vaya con Dios,
Chuck, CABGx3

A Sheet by any other name.....
 

Does the 2nd workbook have more than one worksheet?

If it only has one worksheet then the sheet should be the active sheet.

instead of this
Sheets("BUYER_CB").Select
Sheets("BUYER_CB").Copy After:=ThisWorkbook.Sheets(1)

oldsheetname = activeworksheet.name
newsheetname = activesheet.anme
sheets(newsheetname).select
sheets(newsheetname).copy after:=thisworkbook.sheets(oldsheetname)


"CLR" wrote:

Hi All........
I am opening a second Excel file and copying the only sheet therein back to
my original file. All code works fine, except that it requires me to
hardcode the SheetName of the second file. I would like to be able to
retrieve the sheet regardless of what it might be named.

Here's what I'm using now

Sheets("BUYER_CB").Select
Sheets("BUYER_CB").Copy After:=ThisWorkbook.Sheets(1)


TIA for any assistance
Vaya con Dios,
Chuck, CABGx3

A Sheet by any other name.....
 

Thanks JE..........so simple even I can understand it........and it works!

Vaya con Dios,
Chuck, CABGx3



"JE McGimpsey" wrote:

One way:

Sheets(1).Copy After:=ThisWorkbook.Sheets(1)

Note that the first line isn't required.


In article ,
CLR wrote:

Hi All........
I am opening a second Excel file and copying the only sheet therein back to
my original file. All code works fine, except that it requires me to
hardcode the SheetName of the second file. I would like to be able to
retrieve the sheet regardless of what it might be named.

Here's what I'm using now

Sheets("BUYER_CB").Select
Sheets("BUYER_CB").Copy After:=ThisWorkbook.Sheets(1)


TIA for any assistance
Vaya con Dios,
Chuck, CABGx3

A Sheet by any other name.....
 

Thanks for the response Joel, but I'm using JE's one-liner for now. Your's
is interesting tho, and may prove useful for another application I'm thinking
of....
Thanks again

Vaya con Dios,
Chuck, CABGx3



"Joel" wrote:

Does the 2nd workbook have more than one worksheet?

If it only has one worksheet then the sheet should be the active sheet.

instead of this
Sheets("BUYER_CB").Select
Sheets("BUYER_CB").Copy After:=ThisWorkbook.Sheets(1)

oldsheetname = activeworksheet.name
newsheetname = activesheet.anme
sheets(newsheetname).select
sheets(newsheetname).copy after:=thisworkbook.sheets(oldsheetname)


"CLR" wrote:

Hi All........
I am opening a second Excel file and copying the only sheet therein back to
my original file. All code works fine, except that it requires me to
hardcode the SheetName of the second file. I would like to be able to
retrieve the sheet regardless of what it might be named.

Here's what I'm using now

Sheets("BUYER_CB").Select
Sheets("BUYER_CB").Copy After:=ThisWorkbook.Sheets(1)


TIA for any assistance
Vaya con Dios,
Chuck, CABGx3