VBA opening two worksheets importing different data in each

Question

Hi im new to VBA excel and i want to import data this part i can do fine but in the same sub i want to open a new worksheet in the same workbook and in that worksheet i want to keep the file names of the data i imported from.

Sub GetData()

        Dim path As Variant
        Dim excelfile As Variant
        Dim data_file(1 To 100) As String


        path = "C:\dummmy\"`

        main_file = ActiveWorkbook.Name

        excelfile = Dir(path & "*.xls")

        Do While excelfile <> ""

        data_file(k + 1) = excelfile

         With ThisWorkbook
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Test"
            ActiveSheet.Paste = datafile(k+1)
        End With'

rest of the code the problem i have having is just putting the address of the file in a different worksheet

maybe [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) will help you — litelite, Aug 15 '16 at 20:13
What is it doing that you don't want it to do? Where do you want the filename? Just specify the cell range by row,col if you that's what you want — dbmitch, Aug 15 '16 at 20:29
**Unrelated to your problem** (which, if I understand correctly, is how to work with one worksheet and still be able to write to another worksheet at the same time) - you are using an undeclared variable `datafile` in your second last line of code. I recommend you get used to using the `Option Explicit` command to ensure all variables are declared, thus avoiding typos (which I expect this is). — YowE3K, Aug 15 '16 at 21:10

score 0 · Answer 1 · answered Aug 16 '16 at 09:10

If I understand your question correctly, you want to know how to work with different worksheets within the same Sub?

That's rather straight forward. Instead of using "ActiveSheet" or anything of the sort, just use their qualified equivalents.

Example:

ThisWorkbook.Worksheets("Data").[...]
ThisWorkbook.Worksheets("Filenames").[...]

This syntax will enable you to operate on specific worksheets. You can either use their name (in my example "Data" and "Filenames") or their position (0, 1, 2 , 3, ...) with 0 being the leftmost worksheet.

When using this syntax it won't matter which worksheet is active, you can do operations on all available worksheets.

VBA opening two worksheets importing different data in each

1 Answers1