Combine sheets into one excluding a specific sheet and arrange data accordingly

Hello,

I would like to ask if anyone knows how to combine all sheets in a workbook into one master sheet excluding the "Input" sheet.

Since all other sheets are stock data pulled from yahoo, they contain similar information in the same rows and columns.

I need the combined sheet to look exactly like the "result" sheet in the workbook.

Could you please review and help?

Thanks

I mixed-up the date format with UK's date.

Thank you for the quick response. However, the result when I ran the macro was not the same as my original result tab.

The date is way different and longer than the date range in each tab.
I want to pull the adj close price of each tab into a result tab with columns that associate with the tab name.
If the sheet is blank how can we run a macro that will generate the result as my original result tab? I'm looking at the perspective that the macro will create a new tab that generate the result as my result tab sample.

Okay! My code is completely wrong. I will correct it in a few minutes

I want to be able to create a automatic master/result sheet when we run the macro due to the process.

The process is I will enter the list of stocks in Input sheet then it will retrieve data for each stock from the list. Then I will run your macro to combine them all into another sheet automatically.

Can we modify the code to be able to do that?

So, will the column headers in sheet result come from sheet Input column A or the sheet names?
My question is why do you want to delete sheet Result? What happens to the merged data when you delete that sheet? The code as it is now automatically creates that sheet if it does not exist. If it does, it just clears the cells except the header.

You have dates in all sheets of column A. Which sheet did you use to populate column A in result sheet?

See if this works for you. If not, you need to show me your desired output.

1. If you can generate the header by sheet name except sheet "Input" that would be fine. I think it would be easier for coding when the header come from the input sheet. Either way would be okay.
2. I want to delete the result sheet just in case if I started a brand new sheet and data; I can use your code in any file.
3. You can pull the dates from any stock data sheets as they will be all the same.

Amended one. This ignores sheet Input and picks the names from each sheet.

Could you help address the issue to pull the date from the second sheet but the Input sheet as reference? Your current code has it to pull from the specific sheet with name. I wanted to make it more dynamic because the name may be different when I pull different stock data.

There are only two ways of referring to a sheet. By name( Or code name) or index. Index is the position of the worksheet with in the sheet collection. So, if you want to use index, you have to make sure that sheets are on the same order. If not, it will mess your output.
You only need to change one line on the first code.

Sheets("AVNW").Range("A1:A" & Sheets("AVNW").Range("A" & Rows.Count).End(xlUp).Row).Copy ms.Range("A1")

INTO

Sheets(2).Range("A1:A" & Sheets(2).Range("A" & Rows.Count).End(xlUp).Row).Copy ms.Range("A1")

Thank you very much. I'm sorry that I need to ask if we can sort the column A so that the earliest date appear first and ascending to the latest date at the bottom?

Try the attached.

I have updated the wrong version. This is the correct amendment to the first code.

Thank you very much. It's working well. I can close this post now.