Transferring Data/Auto Sort

[SilkyJohnson]

I am trying to combine rows of data, each 4 cells long from multiple worksheets into one master sheet. I need to know if there is an easy way to do this and also I would like to setup an Auto Sort if possible to automatically sort the rows by date.

The data that will be transferred is: Date, Amount, Description, and Company Billed. I have my workbook organized by a Vendor per worksheet and would like to have the data I enter inside the vendor worksheet to automatically transfer to the master sheet and sort by date. Does that make sense?

[Flintstone]

Hello SilkyJohnson:

It sounds simple enough, if you could post a sample .zip file I'm sure you would get a better response.

Matt

[SilkyJohnson]

Here you go, this is an example of what I have...I have more worksheets in the original. I want to be able to enter info in the Bulbs.com worksheet and have it automatically update the master sheet with that entry and then sort all the data by date.

I would like for it to transfer all entries from each of my worksheets into the master sheet and then sort by date. Can anyone help with this?

Thanks.

[SilkyJohnson]

Here is a better example, with more data to work with.

[VBA Noob]

This might get you started

The main code is from this website

http://www.rondebruin.nl/copy2.htm

VBA Noob

[SilkyJohnson]

Thanks!! That's exactly what I was looking for, plus a cool button! Now I just need to modify the code for my original and I should be good.

Thanks again.

[SilkyJohnson]

I'm not sure why but when I use that same code you have, the output of "Master AMEX Sheet" transfers the column names and one row of data is showing up twice. I even added columns to the workbook that you uploaded for me and everything works fine...but that same code gives me different results. Any idea as to why it would be doing that?

[SilkyJohnson]

Okay, I found the reason for the duplicates...it was a duplicate listing of data on 2 different worksheets. But what about the column names any suggestions?

[VBA Noob]

Read the link and it will help

If you are not starting from A1 then that may be the issue.

Maybe try removing the below code.

I hard coded the headers

Please Login or Register  to view this content.

VBA Noob

[SilkyJohnson]

I deleted that line of code and it is doing the same thing only now it obviously doesn't display those headers. For some reason it copies over the heading from worksheets that do not have any data in them, only headers. Not sure why but, I'll take a look at that link again and see if I can't figure it out. Thanks for the help.

[VBA Noob]

If you still have a problem post a sample of your file.

VBA Noob

[SilkyJohnson]

Everything works out fine except for transferring of the heading of worksheets that don't have any date besides the heading themselves. I believe an IF/ELSE statement could fix this but do not know enough about VBA to implement it into the macro. Something like:

IF (Row 2 is empty)
Do not transfer data from this worksheet
ELSE
Transfer Data and move on to next row

I'm not a programmer or even close, but I do understand the logic somewhat...

Here is a sample of 3 of the worksheets I am using, 2 of them have just the headers in them and data. The other one has the header plus a row of data.

Also, is there any way to add formatting to the code below? I would like to make the heading Bold and add an Outline and Inside Border to all cells with headings?

Please Login or Register  to view this content.