MACRO: How to import data from sheets.

[Jorrg1]

Hi all!

I want to create a macro that transfer data (income statements) from different sheets.

For example:
If I choose department 100 and press "Grab Now" button, the data from department 100 will be transfered to the sheet called "transfer to this sheet".

I have attached my problem Test.xlsx

Here is the case:
- I have one excel file with 5 sheets
- Sheet 1 (Macro Button), has the macro button and a dropdown function where you choose a department of your own choice.
- Sheet 2 (Transfer to this sheet) is where the chosen data shall be transfered to.
- Sheet 3 (100) is department "100
- Sheet 3 (100) is department "101"
- Sheet 3 (100) is department "102"

Thank you very much

Kind Regards

Jørgen Harsem

[Admin]

Hi

Try

    Dim strDept As String
    Dim wksDest As Worksheet
    
    Set wksDest = Worksheets("Transfer to this sheet")  '<< adjust to suit
    
    strDept = Range("f16").Value2
    
    If Len(strDept) Then
        wksDest.UsedRange.Clear
        On Error GoTo 1
        Worksheets(CStr(strDept)).UsedRange.Copy wksDest.Range("a1")
    End If
    Exit Sub
1:
    MsgBox "Worksheet '" & strDept & "' does not exist", vbCritical

[Jorrg1]

ah, it works perfect. Thank you very much

Can you also please explain what dim, strDept, string and Len means?

thanks again

Cheers!

[Admin]

Hi

Have a look at some of these links for a basic understanding of VBA

Excel VBA Basic Tutorial 1

Excel VBA tutorial