Copy and paste Filename to left of data

Hello excel gurus,

I have a spreadsheet that copies and pastes data from excel spreadsheets within a directory. It can copy a set number of columns depending on the known files size. I have the problem, however, that the data is not labelled in any way, shape, or form. Here is the code.

Please Login or Register  to view this content.

Is there any way I can copy and paste the actual filename to the left of the data that is already being copy/pasted?

Any help you guys can give would be most appreciated.

This gives the name of the macro file itself and not the name of the file from which the data was copied from.

Good idea though, I will maybe try to put it in at another point of the code.

I tried to change it

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

But it said it was an invalid command :/

Use this instead

Please Login or Register  to view this content.

I put it in like this.

Please Login or Register  to view this content.

I get an "Object Required" error

I also tried it without the .Name and it gave an "Subscript out of Range" error.

I am very bad with vba I apologize if I used it wrong.

Oh, seems like strFile itself is already the file name. So just use this:

Please Login or Register  to view this content.

Thank you that worked. The only caveat is that it only pastes it once. Sometimes the data has 4 lines of data and it won't paste it in front of the next three lines, is there any way to accomplish that?

Try:

Please Login or Register  to view this content.

Another thing is, the way you are referencing the worksheet is very inconsistent, you have:
Worksheets(1)
Sheet1
Worksheets("Sheet 1")

Not that it is a problem now, but may cause error if your worksheet is no longer named "Sheet 1".

Thank you millz, I will make sure to make it more consistent. That fix worked perfectly.

This change solved the problem but I found that it only worked when I had the worksheet open. If I run it while on a different worksheet it gives me an application-defined or object-defined error.

Try this amended code:

Please Login or Register  to view this content.