Importing Excel Files & Copying Data Horizontally (instead of vertically) Help!

Ignore - misread your post.

Untested.

Please Login or Register  to view this content.

If you can not use transpose what else would change vertical in to horizontal?

I do not know!

I have only looked at your code. Your code as it is now copies in to next empty row. If you want to reverse the copy, you are transposing . A transpose means, convert vertical in to horizontal and vice versal.

Instead of copying the next file into a new row, it needs to be copied into a new column.

because of this line

Please Login or Register  to view this content.

you can only copy to column A. If you want to copy to another column other than column A, you need to remove the entire row and change to say A1:Z

Ah yes, so if my spreadsheet collection is typically 6 columns wide, I can set it to A1:F33 for example, but how would I then have this macro place the data from the next spreadsheet into column H1? G1 would be a blank column to provide a separation between files.

Basically I need the columns to continue building out in --> direction

Hi -

How about this one?

Please Login or Register  to view this content.

event

Hi Event,

Thank you for looking at the code.

I think your changes are on the right track, but it's copying the data on top of itself instead of expanding out to the right. Would it help if I provided exact dimensions? Or maybe we can simplify this to the point that it simply copies columns A:I, pastes them into the current worksheet, inserts a blank column, and begins the loop in the next adjacent column to the blank one?

Add 1 to go to next column

Please Login or Register  to view this content.

Change this line

Please Login or Register  to view this content.

Also change
usedrange in to A1:F13

Originally Posted by AB33

Change this line

Please Login or Register  to view this content.

"Object variable or With block variable not set"

Set sh = Sheets(1)
change it to actual sheet name , not index number. What ever the master name is, it must have a name

I suspect that the master sheet(Sheets(1) is blank.

If it is, use this line

Please Login or Register  to view this content.

Sorry!
My mistake. I forget to link last column to sheet master.

Please Login or Register  to view this content.

In which line of the code the error shows?
It would be easier to solve it if you could attach a sample, so that we can pin point the error.

WinNuke95,
I can not download the file from the site. Where I am interest is when the code opens the files and then copy.
Did you get this error when you copy the entire row? If not, I want to see the code when it errors.

Could you run the code by stepping over, using F8 key and see which line errors.

I want to see the object variable error.

Originally Posted by AB33

WinNuke95,
I can not download the file from the site. Where I am interest is when the code opens the files and then copy.
Did you get this error when you copy the entire row? If not, I want to see the code when it errors.

Could you run the code by stepping over, using F8 key and see which line errors.

I want to see the object variable error.

The object variable error is on this line: lastColumn = sh.Cells.Find("*", , , , xlByColumns, xlPrevious).Column + 1

If I remove this line, I then get:


Run-time error '1004':
We can't do that to a merged cell.

-> sh2.UsedRange.Copy sh.Cells(1, lastColumn)

Please Login or Register  to view this content.

Okay, do you get the same error if you use this line?

lastColumn = sh.Cells(1, Columns.Count).End(xlToLeft).Column + 1.

You need to unmerge cells, no option.
Try this line first.

Please Login or Register  to view this content.

I split the above lines in to 2 rows to separate the error.
Or
try this line

Please Login or Register  to view this content.

Originally Posted by AB33

Okay, do you get the same error if you use this line?

lastColumn = sh.Cells(1, Columns.Count).End(xlToLeft).Column + 1.

You need to unmerge cells, no option.
Try this line first.

Please Login or Register  to view this content.

I split the above lines in to 2 rows to separate the error.
Or
try this line

Please Login or Register  to view this content.

I think part of the problem is I'm not sure what your intention is because we're talking about partial codes here. I may not have enough experience to interpret what you're asking me to do.

If I use: lastColumn = sh.Cells(1, Columns.Count).End(xlToLeft).Column + 1.
And Remove: lastColumn = sh.Cells.Find("*", , , , xlByColumns, xlPrevious).Column + 1

Then my error points to: sh2.UsedRange.Copy sh.Cells(1, lastColumn).
Resulting error:
Run-time error '1004':
We can't do that to a merged cell.


Regarding the coping and pasting of merged cells, if you see my original post, the code works perfectly fine for a 5x10 table containing merged cells above the table. The macro copies and pastes everything exactly as it was found in the C:\Temp files and inserts blank rows to visually separate each file's data.


When I add the sh2.Cells.UnMerge code, I get the following error:
"Application-defined or object-defined error"

Please Login or Register  to view this content.

I'm pasting the entire code each time so you can see where I've made changes to determine if I've failed to follow your instructions.

Well,
My intention is very simple. Since I could not see your sample, what ever I have written is a guess work.
If your master sheet is blank this code will not work.
lastColumn = sh.Cells.Find("*", , , , xlByColumns, xlPrevious).Column + 1

I assumed you have data in master sheet.
I then had given you another code which can work with empty master sheet.

Please Login or Register  to view this content.

You also said, usedrange is not working, so I gave you another option.

Please Login or Register  to view this content.

Which copies column A1-H13 until the last row to see if it works.

I have also asked you to attach a sample book, but you have sent me a link. If you had attached the sample, It would have been resolved long time ago, with out going to all these circles.

Apologies, I misunderstood. I thought by providing a link to the file would satisfy the sample request. I've attached the sample file.

It's a basic income statement. The account names are in column C. The column heading is merged, and two of the titles in column B may pose an issue if the macro is ending based upon empty cells. It's not entirely necessary to copy these two cells, but I thought there would be a way to simply define which cells to copy (even if blank) and paste them into the master sheet.

My master sheet is blank. I run the macro from my desktop to compile all of the files in the c:\temp folder.

The original range of A1:H13 pertained to a different set of data. I attempted to modify it based upon the attached but it did not succeed.

Thanks for your patience and effort - it's appreciated.

Hi -

Here is my updated codes;

Please Login or Register  to view this content.

event

Thank you!!