| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| excel sheet bootom half sheet goes behind top part of sheet | Excel Worksheet Functions | |||
| Duplicate sheet, autonumber sheet, record data on another sheet | Excel Worksheet Functions | |||
| Export sheet store sheet import sheet. | Excel Programming | |||
| relative sheet references ala sheet(-1)!B11 so I can copy a sheet. | Excel Discussion (Misc queries) | |||
| 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 | Excel Programming | |||
Linear Mode
