VBA Help please

[NateD1]

Hi All, hope you can help me, I'm trying to find an easy way to return 10 individual cell values using VBA from one workbook to another.
The data is stored in a closed workbook(workbook 1), each month a new tab is created in workbook 1, in same format just renamed Nov,Dec etc..

I need the Cell value from 10 rows (i.e A1,A5,A9) from workbook 1. so that when i need to pull a particular month data into Workbook2, i can just change Nov to Dec in a cell, press a button and it will pull from the correct tab name in work book1. is this possible to do? if so can some help with a VBA. i have tried using INDIRECT however the other workbook is always closed
thank you.

 

[mohadin]

Hi,
Some thing Like

Sub test()
Dim myfilename, strWsName
Dim a As Variant
Dim wb As Workbook
    myfilename = "d:\workbook1.xlsx"
    strWsName = ActiveSheet.Range("i1")
    Set wb = Workbooks.Open(myfilename)
    With wb.Sheets(strWsName)
        a = .Cells(1, 1).Resize(9)
    End With
    wb.Close False
    Cells(1, 1).Resize(UBound(a)) = a
End Sub

 

[NateD1]

thankyou, Are you able to let me know what the above does? thanks

 

[NateD1]

thankyou, Are you able to let me know what the above does? thanks

it works by pulling the coloumn from the sheet i need it to, how can i adapt it so that when the sheet name changes in the file it pulls from, that it looks up that sheet name and pulls the data?

 

[mohadin]

Well
In range("i1") the (Nov to Dec)
In the above code you have to change <<myfilename>> to where your Workbook1 is stays
And run the macro

 

[NateD1]

Well
In range("i1") the (Nov to Dec)
In the above code you have to change <<myfilename>> to where your Workbook1 is stays
And run the macro

perfect thank you, if i want to extend the number of rows it pulls how do i do that? and lastly how to i choose where those rows are pasted into?

 

[mohadin]

a = .Cells(1, 1).Resize(9)

In this start from A1 down to A9
So cells(1,1) refers to A1
This for pulling

For past the last line in the code
Again you need to specify the first range witch is

Cells(1,1)=Range("A1")

the rest of the row will be taken car by the code

 

[NateD1]

a = .Cells(1, 1).Resize(9)

In this start from A1 down to A9
So cells(1,1) refers to A1
This for pulling

For past the last line in the code
Again you need to specify the first range witch is

Cells(1,1)=Range("A1")

the rest of the row will be taken car by the code

amazing thank you

 

[mohadin]

You are welcome
Thank you for the feedback
Be happy