How To Copy Data By Worksheet Name

**TomyLee** · 08-04-2013, 02:24 AM

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** · 08-04-2013, 09:12 AM

Hi

Code:

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** · 08-04-2013, 11:24 AM

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** · 08-04-2013, 11:50 AM

@Admin,

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

**Admin** · 08-04-2013, 11:54 AM

Hi

Put this code in 'Main' sheet module.

Code:

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