ActiveSheet.Paste not working on certain pc's??

[stevie500]

Hi, can someone urgently help me.
i'm copying data from 1 book to the other by copying all the data from the selected file, opening a new book, select cell A1 then paste my data. This is succesful apart from on 2 pc's.
In debug mode after the error message if i proceed it still works!!

Does anyone know why this happens??
code:-

Workbooks.Add
Columns("C:C&quot.Select 'format this column
Selection.NumberFormat = "@"
Range("A1&quot.Select
ActiveSheet.Paste <-------- bombs out here

 

[rmikesmith]

Stevie500,

When I try to run this I also get an error at the paste step. If I single-step through the code, it appears that Excel is canceling the copy mode after formating column C in the new workbook. While you could get around this by changing the order of operations, I suggest trying the following, which is equivalent to selecting Edit->Move or Copy Sheet... from the menu then choosing <new book> as the destination and checking the Copy checkbox:

Activesheet.Copy  'copies the active sheet to a new workbook
Columns("C:C").Select
Selection.NumberFormat = "@"

Hope this helps

 

[stevie500]

thanks for the help.

i tried reordering the sequence of events - didn't work!
so i've gone for your other option but am having difficulty when i try to delimit the file. i need it to be dilimmited by commas and tabs. the parameter of open only has an option for 1 item, and the textToColumns asks whether i want to replace the cells - i obviously don't want this !!

do you have any ideas for these problems???

thanks
stevie500

 

[rmikesmith]

Stevie500,

Need more info: What file are you trying to delimit? Is this a previously saved text file that you are trying to open with

Workbooks.Open

? If so, what is the source of the file and how was it saved? If you could post some additional code, that would help also.

Regards,

M. Smith

 

[stevie500]

it is a text file created from a C++ application. i was tying to open it as WorkBook.Open.
Anyways i reverted back and put a DoEvents before the paste - seems to work.

thank you
stevie500

 

[rmikesmith]

Stevie500,

If the textfile has some structure to it, you can use

Workbooks.OpenText

to open it into an Excel workbook. The OpenText method allows more options than the Open method, including multiple delimiters (i.e. just like if you were doing this manually and using the Text Import Wizard). Good Luck!

Regards

M. Smith