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:

​

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]Select Month[/TD]
[TD="align: center"]Actual Cost[/TD]
[TD="align: center"]Budget[/TD]
[TD="align: center"]Difference[/TD]
[/TR]
[TR]
[TD]Direct Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Indirect Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total Cost[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Profit Margin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]January [/TD]
[TD="align: center"]Actual Cost[/TD]
[TD="align: center"]Budget[/TD]
[TD="align: center"]Difference[/TD]
[/TR]
[TR]
[TD]Direct Cost[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Indirect Cost[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Total Cost[/TD]
[TD="align: center"]300[/TD]
[TD="align: center"]400[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD]Profit Margin[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD="align: center"]310[/TD]
[TD="align: center"]410[/TD]
[TD="align: center"]100
[/TD]
[/TR]
</tbody>[/TABLE]


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.