Macro: Copy to Sheet1 data from Sheet2, including specifications.

[Andreiita00]

Hi Everyone,
This is my first post on the community, hope dont break any rule.

In Sheet1, i have a table like this:

​

Select Month	Actual Cost	Budget	Difference
Direct Cost
Indirect Cost
Total Cost
Profit Margin
Total

<tbody>
</tbody>

In Sheet2, i have of the same, 12 tables, but filled with data.

January	Actual Cost	Budget	Difference
Direct Cost	200	250	50
Indirect Cost	100	150	50
Total Cost	300	400	100
Profit Margin	10	10	0
Total	310	410	100

<tbody>
</tbody>

I dont know how to do it here, but in Excel i have January (2columns) Febaruy (2columns) March tables. filled each table with its determinated data, and going down i have 3 more months, and so etc till i complete 12 tables one per month.

So now what i need is: That in Sheet1, where is Select Month be some kinda of drop down list, which allow me to pick the month i wanna see, and then when i pick. for example: April, then go to Sheet2, find the April table, and bringthe values to fill the table in Sheet1 , whatever option be, flirting, or copying just values, or filling the table. I really dont know what be easier.And then if i pick another month like October, do the same, etc.

Im really not an expert on Excel, so i hope i be clear enough in what i need and wish someone could help me to do a macro that work, please be detailed in explanation since i dont understand much about macros

Thanks Everyone. Looking forward for fast answer, since im short in time, i tried look forums and do it by myself, but it didnt work out, now time passed and i have nothing. So here Im asking for your help. Thank You~

 

[RickXL]

If I understand you correctly then you probably don't need a macro.

What I did was this:

I put your "Select Month" table on Sheet1
I added the 12 monthly tables to Sheet2. All arranged underneath each other, Sheet2!A1=January and Sheet2!A7=February, Sheet2!A13=March etc.
On Sheet3 I added a list of the months from January to December in A1:A12.

Then I added Data Validation to Sheet1!A1.
Goto Data-->Data Validation-->Data Validation
Then set Allow: to List
Then set Source: to =Sheet3!A1:A12

You should now have a working dropdown menu on Sheet1 that allows you to select a month from the list on Sheet3.

Now add this formula to cell Sheet1!B2
=OFFSET(Sheet2!B2,6*(MATCH($A$1,Sheet3!$A$1:$A$12,0)-1),0)

MATCH($A$1,Sheet3!$A$1:$A$12,0) converts the month name into a number January=1 etc. Note: The list of months on Sheet3 needs to have the same months in the same order as those on Sheet2.

6*(MATCH($A$1,Sheet3!$A$1:$A$12,0)-1
works out which row has the data for which month.

Now drag the formula in cell Sheet1!B2 across to D2 and down to D6.

You should now have a table on Sheet1 with a dropdown in the top left hand corner which when you choose a month it copies the data from the correct place on Sheet2 into Sheet1.