Rearrange multiple columns in four columns, VBA

[eguzun]

Hello guys,
An excel rookie needs the help of more experienced users of Excel and VBA.

I have a data set that looks the following way:
SAMPLE.xlsx

There are around 600 companies in the data set. I would like to arrange those companies and their four columns of data into only four columns. In other words, I want all the data to be arranged only in columns A-D, no data starting from column E. Note: the amount of data for each company differs; I can have 10 years of data for one company, and only 2 months of data for another company.

I look forward to hearing from you soon. Thank you so much in advance!

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[eguzun]

Thank you so much for getting back to me so quickly! I attached an excel file with a BEFORE and AFTER sheet.

[JohnTopley]

Please Login or Register  to view this content.

Click "RUN" on "After"

[eguzun]

Thank you! This is impressive. I do get the following message/error though:

Run-time error '1004:
You can't paste this here because the Copy area and paste are aren't the same size.

Select just one cell in the paste area or an area that's the same size, and try pasting again.

What to I need to change in the following row: inRng.Copy Destination:=ws2.Cells(lr2, "A") ?

[daboho]

Please Login or Register  to view this content.

[JohnTopley]

Post file with errors as I don't know why the areas are not the same. Are there merged cells somewhere?

nRng.Copy Destination:=ws2.Cells(lr2, "A")

.... where ?

If you want to start in column other than "A" then change "A" to required column .

Lesson here: post a file that represent what the real outcome required is.

[eguzun]

I believe I know what the problem might be. My data set is simply too large. The data for approximately 80% of my sample companies is rearranged perfectly using your code (the data reached the maximum of 1,048,576 rows). I will try to use a smaller data set. Thank you so much for your help, John!

[JohnTopley]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

EDIT: it should possible to split the into more than one sheet: is that acceptable?