Solved: Question on sheet names and Copy

[mduff]

Hi all,

Please see the code below I have 2 questions one is that the sheet name Expedia_Today_IDP[1].xls can change to Expedia_Today_IDP(something else) it is an export form a web site but will always have Expedia_Today_IDP in it is there a way to tell the code to activate the sheet called Expedia_Today_IDP*.xls * being any characters after the Expedia_Today_IDP

The second question is after I have the sheet open I am trying to copy the contents of the whole sheet it and paste in an other workbook I thought I had the copy down in the part in bold but it keeps giving errors (please see the part in bold)

Any help would be appreciated and please let me know if you need any more information

thanks
[vba]
Workbooks("Expedia_Today_IDP[1].xls").Activate
ActiveSheet.Cells.Select.Copy _
Destination:=Workbooks("Expedia_IDP.xls").Sheets("Expedia Today IDP") _
.Range("A1:A3") Workbooks("Expedia_Today_IDP[1].xls").Close
Workbooks("Expedia_IDP.xls").Activate
Range("A1:A3").Select
[/vba]

Edited 24-Jul-07 by geekgirlau. Reason: insert line breaks

[malik641]

Hi mduff, welcome to VBAX

Please see the code below I have 2 questions one is that the sheet name Expedia_Today_IDP[1].xls can change to Expedia_Today_IDP(something else) it is an export form a web site but will always have Expedia_Today_IDP in it is there a way to tell the code to activate the sheet called Expedia_Today_IDP*.xls * being any characters after the Expedia_Today_IDP

Well, it would be kind of difficult to determine the next filename that's going to be given when you export the file from the web site. You can activate the workbook (from the web) yourself and then run the macro (you don't need to use the Select statement for what you're doing).

The second question is after I have the sheet open I am trying to copy the contents of the whole sheet it and paste in an other workbook I thought I had the copy down in the part in bold but it keeps giving errors (please see the part in bold)

[vba]Workbooks("Expedia_Today_IDP[1].xls").Activate
ActiveSheet.Cells.Select.Copy Destination:=Workbooks("Expedia_IDP.xls").Sheets("Expedia Today IDP").Range("A1:A3") Workbooks("Expedia_Today_IDP[1].xls").Close
Workbooks("Expedia_IDP.xls").Activate
Range("A1:A3").Select[/vba]

The problem is because you're selecting every single cell from the web site worksheet and trying to paste into a block of 3 cells. The cells that are being copied have to match the block of cells you are pasting at OR you can just use a single cell as a paste area (regardless of the size of cells copied).


So what I would do is to have the code in the "Expedia_IDP.xls" workbook and you can refer to it (in code) as ThisWorkbook (which basically uses the host workbook of the module the code is in). As for the other workbook, you can use ActiveWorkbook rather than the name of the workbook itself. Also, for the copied range, I'm not sure where the data you need is located, but if all the data is a block of 3 cells (in one column) and there is no more data in the worksheet, then you can use:
[VBA]Sub CopyFromActiveWorkbook()
Dim ActiveWB As Excel.Workbook
Set ActiveWB = ActiveWorkbook

' Don't need following line
' Workbooks("Expedia_Today_IDP[1].xls").Activate

' ActiveSheet.Cells.Select.Copy Destination:=Workbooks("Expedia_IDP.xls").Sheets("Expedia Today IDP").Range("A1:A3")
ActiveWB.ActiveSheet.UsedRange.Copy Destination:=ThisWorkbook.Sheets("Expedia Today IDP").Range("A1:A3")

' Workbooks("Expedia_Today_IDP[1].xls").Close
ActiveWB.Close False

'Workbooks("Expedia_IDP.xls").Activate
ThisWorkbook.Activate

ThisWorkbook.Sheets("Expedia Today IDP").Range("A1:A3").Select
End Sub[/VBA]

To use this just activate (manually) the workbook you want to copy from and run the macro. Let me know how it goes

Hope you enjoy VBAX

[mduff]

Thanks a lot that worked!!!!

[malik641]

Of course it did

Glad to help out