Error 1004 when pasting from clipboard

**~TaC~** · 01-03-2021, 03:18 PM

Hi,

This question was asked in another thread but as its actually a separate issue I have marked that as solved and starting this one

https://www.excelforum.com/excel-pro...to-number.html

The code below is used from a button where 4 macros run in sequence

I have a page of data at work not sure what the source is but when I copy it straight in to excel it doesn't copy right, when I copy and paste in to notepad and then copy that and paste in to excel it works hence why my code has the sequence it does.

Please Login or Register  to view this content.

When I first open the workbook data copies correctly to Sheet1 and then into sheet(Variances) as required, when I then go to paste more data I get the Error 1004 Paste method of Worksheet class failed

I know there has been a lot of posts about this and I have search many and tries many things but can't work it out.

Directly after closing the error message I Ctrl+V and data pastes so I know clipboard is not empty, I'm thinking maybe a focus/active window issue

So just to clarify, open excel file sample1 and txt file sample2, copy data in txt file go back to excel Variances sheet and click button 'Copy to Paste Data', should copy ok. Open again the txt file copy data and click button in excel and error occurs

Any help please

**rorya** · 01-04-2021, 06:06 AM

I think you'd be better off figuring out a way that doesn't require shelling Notepad and then using SendKeys repeatedly. Can you post a workbook showing what happens when you paste directly from the source page?

**~TaC~** · 01-04-2021, 05:59 PM

Thanks for your reply, I have tried to work out how I could do it without shelling Notepad but it just doesn't seem to work. As I remember it has formatting issues where it puts everything in one long line into the selected cell. Hard to get a sample fine from work but I've simulated it with the files uploaded.

Using sample2.txt as the source it pastes into another Notepad page, then into Sheet1 of sample1.xlsm and then to Variances sheet. Keeping WB open when copying sample2.txt and clicking button a second time the error occurs. This is exactly what happens with the source data at work.

**~TaC~** · 01-05-2021, 05:38 PM

I think I found something that might help, with the attached files if you open both and copy the data in the table.pdf and click the 'click to paste data' button on sample1.xlsm then data pastes in my WB on six rows but into first column of range.

If I paste same data into a blank worksheet then data pastes into one cell as a long string, so in my WB half the problem is solved by getting the right number of rows.

If I use text to column, delimited and separate by space on blank sheet then it separates in to columns on one row but also separates text that should be in one cell.

How would I get the 4 rows and 6 columns to pastes in my WB? If this can be resolved then it could fix my problem without shelling to Notepad

If it helps, and might be seen in an earlier upload. Columns are always as follows:

E: always date
F:H currency amount no spaces
I: contains spaces but always one of 6 fixed entries
J: everything else, custom user input