Copying cells from different sheet based on a cell value

[Nelsini]

Hello!

I have an excel book that contains a sheet for every month (sheet name is the month it relates to) where I register different expenses throught each month. Then, I have 2 other sheets, one for summarising the data of those 12 sheets and another with charts.

I have 2 cells on the data sheet, cell K4 where it says the month the most money was spent on, and cell L4 where it shows the value regarding that month. Example:
MONTH WITH MOST EXPENDITURE

Month	Value
May	20000€

On each sheet I have 6 cells regarding sums of different tables, always from B2:E4 in every sheet (example attached).

The problem is, I want to copy those cells, based off K4 on the data sheet, and then the attached print would be copied to below that table. Is it possible? I don't know if I'm making myself clear..

Thank you in advance to anyone willing to help!

 

[Nelsini]

I've found a solution, but it's really not optimal since I don't understand VBA that much:

Sub CopyMonthTotal()

Dim Month As String

Month = Worksheets("Data").Range("K4").Value

If Mes = "Jan" Then
    Worksheets("Jan").Range("B2:E4").Copy Worksheets("Data").Range("K6")

If Mes = "Feb" Then
    Worksheets("Feb").Range("B2:E4").Copy Worksheets("Data").Range("K6")
    
If Mes = "Mar" Then
    Worksheets("Mar").Range("B2:E4").Copy Worksheets("Data").Range("K6")

If Mes = "Apr" Then
    Worksheets("Apr").Range("B2:E4").Copy Worksheets("Data").Range("K6")
    
If Mes = "May" Then
    Worksheets("May").Range("B2:E4").Copy Worksheets("Data").Range("K6")

ElseIf Mes = "Jun" Then
    Worksheets("Jun").Range("B2:E4").Copy Worksheets("Data").Range("K6")

If Mes = "Jul" Then
    Worksheets("Jul").Range("B2:E4").Copy Worksheets("Data").Range("K6")
    
If Mes = "Aug" Then
    Worksheets("Aug").Range("B2:E4").Copy Worksheets("Data").Range("K6")

If Mes = "Sep" Then
    Worksheets("Sep").Range("B2:E4").Copy Worksheets("Data").Range("K6")
    
If Mes = "Oct" Then
    Worksheets("Oct").Range("B2:E4").Copy Worksheets("Data").Range("K6")

If Mes = "Nov" Then
    Worksheets("Nov").Range("B2:E4").Copy Worksheets("Data").Range("K6")

If Mes = "Dec" Then
    Worksheets("Dec").Range("B2:E4").Copy Worksheets("Data").Range("K6")

Else
    Worksheets("Jan").Range("B2:E4").Copy Worksheets("Data").Range("K6")

End If

End Sub

Hope it helps anyone needing a similar answer!

 

[Nelsini]

Correct code is as follows: (forgot to ad ElseIf instead of If on other lines haha)

Sub CopyMonthTotal()

Dim Month As String

Month = Worksheets("Data").Range("K4").Value

If Month = "Jan" Then
    Worksheets("Jan").Range("B2:E4").Copy Worksheets("Data").Range("K6")

ElseIf Month = "Feb" Then
    Worksheets("Feb").Range("B2:E4").Copy Worksheets("Data").Range("K6")
    
ElseIf Month = "Mar" Then
    Worksheets("Mar").Range("B2:E4").Copy Worksheets("Data").Range("K6")

ElseIf Month = "Apr" Then
    Worksheets("Apr").Range("B2:E4").Copy Worksheets("Data").Range("K6")
    
ElseIf Month = "May" Then
    Worksheets("May").Range("B2:E4").Copy Worksheets("Data").Range("K6")

ElseIf Month = "Jun" Then
    Worksheets("Jun").Range("B2:E4").Copy Worksheets("Data").Range("K6")

ElseIf Month = "Jul" Then
    Worksheets("Jul").Range("B2:E4").Copy Worksheets("Data").Range("K6")
    
ElseIf Month = "Aug" Then
    Worksheets("Aug").Range("B2:E4").Copy Worksheets("Data").Range("K6")

ElseIf Month = "Sep" Then
    Worksheets("Sep").Range("B2:E4").Copy Worksheets("Data").Range("K6")
    
ElseIf Month = "Oct" Then
    Worksheets("Oct").Range("B2:E4").Copy Worksheets("Data").Range("K6")

ElseIf Month = "Nov" Then
    Worksheets("Nov").Range("B2:E4").Copy Worksheets("Data").Range("K6")

ElseIf Month = "Dec" Then
    Worksheets("Dec").Range("B2:E4").Copy Worksheets("Data").Range("K6")

Else
    Worksheets("Jan").Range("B2:E4").Copy Worksheets("Data").Range("K6")

End If

End Sub

 

[Fluff]

You can slim that down like

Sub CopyMonthTotal()

Dim Month As String

Month = Worksheets("Data").Range("K4").Value
If Evaluate("isref('" & Month & "'!A1)") Then
   Worksheets(Month).Range("B2:E4").Copy Worksheets("Data").Range("K6")
Else
   MsgBox "Sheet " & Month & " doesn't exist"
End If
End Sub