VBA Help please

NateD1

New Member
Joined
Apr 1, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
781
Office Version
  1. 2013
Platform
  1. Windows
Hi,
Some thing Like
VBA Code:
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

New Member
Joined
Apr 1, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
thankyou, Are you able to let me know what the above does? thanks
 

NateD1

New Member
Joined
Apr 1, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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-known Member
Joined
Mar 22, 2015
Messages
781
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Apr 1, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 22, 2015
Messages
781
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

VBA Code:
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
VBA Code:
Cells(1,1)=Range("A1")
the rest of the row will be taken car by the code
 
Solution

NateD1

New Member
Joined
Apr 1, 2020
Messages
27
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
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
VBA Code:
Cells(1,1)=Range("A1")
the rest of the row will be taken car by the code
amazing thank you
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
781
Office Version
  1. 2013
Platform
  1. Windows
You are welcome
Thank you for the feedback
Be happy
 

Watch MrExcel Video

Forum statistics

Threads
1,118,270
Messages
5,571,238
Members
412,372
Latest member
JON_ROCKS
Top