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 1322

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 (http://www.anthony-vba.kefra.com/vba/vbabasic1.htm)

Excel VBA tutorial (http://excelvbatutor.com/vba_tutorial.html)