Workbooks.Open Filename:="C:\Ex

[jaydeebe]

Workbooks.Open Filename:="C:\Export\Safeacc.xls"
Columns("A&quot.Select
Selection.Copy
Windows("testing4.xls&quot.Activate
Columns("A&quot.Select
ActiveSheet.Paste

runtime error1004 select range method of class failed

I'M SURE THIS USED TO WORK BUT NOT ANYMORE. i HAVE IT SO THAT YOU CLICK A BUTTON AND IT OPENS AND COPIES DATA FROM A-D COLUMNS IN ONE FILE TO THE OTHER WHERE THE PROGRAMED BUTTON IS.

 

[jaydeebe]

You are correct with the description, unfortunately the code doesn't run beyond opening the file. The following line is highlighted.

wbSafe.Worksheets("safeacc.xls&quot.Columns("A&quot.Copy _
Destination:=wbMe.Worksheets("testing.xls&quot.Range("A1&quot

run time error 9 - subscript out of range

 

[xlbo]

ok - look real hard here - a worksheet doesn't have an xls extension...

wbSafe.Worksheets("safeacc.xls&quot.Columns("A&quot.Copy

you are trying to reference a workbook with a workSHEET....twice

wbSafe is the workBOOK - what is the workSHEET ??



Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jaydeebe]

ok, probably my fault for saying work sheet in a previous post, i was refering to a sheet.

 

[xlbo]

have you tried Loomah's code ??

Simply change the CAPS text to the name of the relevant worksheets and you should be away

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[jaydeebe]

yes, see previous reply.

The following line is highlighted.

wbSafe.Worksheets("safeacc.xls&quot.Columns("A&quot.Copy _
Destination:=wbMe.Worksheets("testing.xls&quot.Range("A1&quot

run time error 9 - subscript out of range

does it matter what OS i use, i've tried this on both win95 and win2000?

 

[Loomah]

Jay
OS will make no difference (I'm using xl2k on win95).

Look more closely at xlbo's post re sheets and books.

What you are doing is giving a workBOOK name where a workSHEET name is required.

When you open safeacc.xls manually how many sheet tabs do you have at the bottom of Excel (Sheet1, sheet2 etc)
Of these which one do you have to click on if you want to copy the data manually?
The name on that tab is what needs to go where you have Worksheets("safeacc.xls&quot or where I put ("PUT_YOUR_SHEETNAME_HERE&quot

In both cases are these sheets actually WORKsheets ie with rows & columns and cells as opposed to chart sheets or dialog sheets etc.

;-)
In testing.xls what is the name of the sheet tab where you want to paste the data? That is the name that needs to go where you have put Worksheets("testing.xls&quot or where I put ("DESTINATION_SHEET_NAME&quot

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[jaydeebe]

Ok, it works! I would like to thank everyone for staying with me through this and i'm truely sorry for any confusion and stress i may have caused with my VBA and Excel ineptness.

What i have been doing this for is to allow our marketing dept to transform a list of postcodes to the format that they use for sector lists. The format that they are recieved in is unsuitable (contains spaces in odd places). Once the data is copied into the sheet a hidden column removes the last two characters. These values are then copied to another column and have all spaces removed before finally adding one back in before the last character. I will now be trying to export this data to Access for comparison with other existing lists.

Many thanks for all your help!

Jaydeebe BSC (hons) MCP