Fill data in table from different worksheets based on cell value

[jb]

Hi Helpers,

I have a excel file table testing with 3 worksheets - tt, mon and tue
All worksheet has same table format.

Now tt sheet's table is empty. I have created mon and tue sheets with data in table. As it is a sample, I have two sheets otherwise there will be 6 sheets for 6 days.

I want to create a drop down in tt sheet with 6 days names. And based on user's choice of day it should fill data from respective sheet's table.
i.e. if user selects tuesday from day list then table of tt sheet should be filled with data from tue sheet.

Two things are to be taken care off
1. It should paste only values not any links in table of tt sheet
2. It should not disturb any conditional formatting of table of tt sheet. (This is sample so I have kept only required part)

Thanks in advance

 

[AlanSidman]

Here is the code for your sample. You should be able to expand it to include the remaining five days. Additionally, to make it work, I had to unmerge all cells. VBA does not like nor work easily with merged cells.

Also, I have included your ws back with an ActiveX combo box inserted.

Option Explicit

Sub JB()
    Dim s1 As Worksheet
    Dim ws As Worksheet
    Dim Crit As String
    Set s1 = Sheets("tt")
    Crit = Range("Y1")
    s1.Range("B9:R23").ClearContents
    If Crit = "Monday" Then
        Sheets("MON").Range("B10:R24").Copy
        s1.Range("B9").PasteSpecial xlPasteValues
    ElseIf Crit = "Tuesday" Then
        Sheets("TUE").Range("B10:R24").Copy
        s1.Range("B9").PasteSpecial xlPasteValues
    End If

End Sub

 

[jb]

Well, I received your reply. I downloaded updated excel file. but when i am trying to change day from combo box to monday or tuesday, the content on tt sheet does not change. Code doesn't run automatically on changing day.

Help.

 

[AlanSidman]

put this code in the worksheet tt as a combobox change event. Do not put it in a module.

Option Explicit

Private Sub ComboBox1_Change()
    Dim s1 As Worksheet
    Dim ws As Worksheet
    Dim Crit As String
    Set s1 = Sheets("tt")
    Crit = Range("Y1")
    s1.Range("B9:R23").ClearContents
    If Crit = "Monday" Then
        Sheets("MON").Range("B10:R24").Copy
        s1.Range("B9").PasteSpecial xlPasteValues
    ElseIf Crit = "Tuesday" Then
        Sheets("TUE").Range("B10:R24").Copy
        s1.Range("B9").PasteSpecial xlPasteValues
    End If
End Sub

 

[jb]

No, it will not run automatically. You will need to either add a command button or click on Alt F8 and run the macro. A worksheet change event does not work with a combo box.

Thanks. I created one button and assigned macro to it. Now it works. Thanks again.

But just few questions:
1. After running a macro, the day sheet from which data is copied, shows the table marked in a copy status. I found the solution to add "Application.CutCopyMode = False". Is it ok? It works actually.
2. tt table data is shown in selected data status. can it be shown normally ?
3. How you fetched data from combo box in a cell Y1? Just want to know.

 

[AlanSidman]

See post above your last to make it change automatically. Now to your question. In the property for the combo box see the attached picture.

 

[AlanSidman]

Put this line of code before End Sub

Application.CutCopyMode = False