How To Copy Data By Worksheet Name

[TomyLee]

Hi,

My workbook has sheet "Main" and other sheets with names like Oct-2012, Nov-2012 ....Ian-2013... Dec-2020)

Sheet "Main" is a report, all other sheets are hidden.

In B7 you can choose "All" or Name1, Name2....Name27
In B2 you can choose January, February ....December
In D2 you can choose 2010, 2011, .....2020

If in B7 is "ALL" and in B2 (month) October and in D2 is 2012 Then:
It will copy range(B8:B34) of sheet Oct-2012 in "Main" worksheet in Range (B8:B34).
It will copy range(C8:Q34) sheet Oct-2012 in the "Main" sheet Range (D8:R34).

If B7 is "ALL" and in B2 B2 (month) Novembre and D2 is 2012 then:
clean range(B8:B34) and Range (D8:R34) in sheet "Main"
It will copy range (B8: B34) of sheet Nov-2012 in "Main" worksheet in Range (B8: B34).
It will copy range (C8: Q34) sheet Nov-2012 in "Main" worksheet in Range (D8: R34).

and so on ...

if in B7 if you not choose "All" then will be no data in range(B8:B34) and Range (D8:R34).

Thanks for any help.

[Admin]

Hi


Option Explicit

Dim ShtMain As Worksheet

Sub kTest()

Set ShtMain = Worksheets("Main")

With ShtMain
If .Range("b7").Value = "All" Then
CopyPaste Left$(.Range("b2").Value, 3) & "-" & .Range("d2").Value
Else
.Range("b8:b34").ClearContents
.Range("d8:r34").ClearContents
End If
End With

End Sub

Sub CopyPaste(ByVal ShtName As String)

Dim Sht As Worksheet

On Error Resume Next
Set Sht = Worksheets(ShtName)
If Err.Number <> 0 Then
MsgBox "Sheet '" & ShtName & "' could not be found.", vbCritical + vbOKOnly
Exit Sub
End If

On Error GoTo 0

With ShtMain
.Range("b8:b34") = Sht.Range("b8:b34").Value2
.Range("d8:r34") = Sht.Range("c8:q34").Value2
End With

End Sub

and call the kTest macro

[TomyLee]

Hello Admin,

Thank you. Excellent. Code is excellent. Thank you very much.
How can I modify the code to be executed on every change in cell B7, B2 and D2.

[TomyLee]

@Admin,

I managed to make the code to run automatically at each change of cell B7, B2 and D2.
Once again thank you.

[Admin]

Hi

Put this code in 'Main' sheet module.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Address(0, 0)
Case "B2", "B7", "D2": kTest
End Select

End Sub

[TomyLee]

Admin,

Thank you very much.