Get Values From Other Sheet Using VBA - Stack Overflow

1. 1. Home
   2. Questions
   3. Tags
   4. Users
   5. Companies
   6. Labs
   7. Jobs
   8. Discussions
   9. Collectives

   10. Communities for your favorite technologies. Explore all Collectives

2. Teams

   Ask questions, find answers and collaborate at work with Stack Overflow for Teams.

   Try Teams for free Explore Teams
3. Teams

4. Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Get early access and see previews of new features.

Learn more about Labs Get values from other sheet using VBA Ask Question Asked 14 years, 1 month ago Modified 4 years, 5 months ago Viewed 563k times 42

I want to get values from other sheets.

I have some values in Excel (sheet2) for example:

A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6

I sum each column in row 4.

I'm working with these values in sheet2 but I want to get the result in sheet1.

When using my code in sheet2 I get the correct answer but when I try to use it in a different sheet I get the result of the values corresponding to the current sheet cells and not to sheet2.

I'm using With Application.WorksheetFunction.

How can I set sheet2 as the active sheet?

• excel
• vba
• worksheet-function

Share Improve this question Follow edited Jul 17, 2019 at 13:52 CommunityBot 111 silver badge asked Nov 10, 2010 at 20:29 Apollon1954Apollon1954 1,39644 gold badges1616 silver badges3333 bronze badges Add a comment |

7 Answers 7

Sorted by: Reset to default Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first) 59

Try

ThisWorkbook.Sheets("name of sheet 2").Range("A1")

to access a range in sheet 2 independently of where your code is or which sheet is currently active. To make sheet 2 the active sheet, try

ThisWorkbook.Sheets("name of sheet 2").Activate

If you just need the sum of a row in a different sheet, there is no need for using VBA at all. Enter a formula like this in sheet 1:

=SUM([Name-Of-Sheet2]!A1:D1) Share Improve this answer Follow edited Nov 10, 2010 at 20:41 answered Nov 10, 2010 at 20:35 Doc BrownDoc Brown 20k77 gold badges5454 silver badges9393 bronze badges Add a comment | 22

That will be (for you very specific example)

ActiveWorkbook.worksheets("Sheet2").cells(aRow,aCol).Value=someval

OR

someVal=ActiveWorkbook.worksheets("Sheet2").cells(aRow,aCol).Value

So get a F1 click and read about Worksheets collection, which contains Worksheet objects, which in turn has a Cells collection, holding Cell objects...

Share Improve this answer Follow answered Nov 10, 2010 at 20:35 jpinto3912jpinto3912 1,46522 gold badges1212 silver badges2020 bronze badges Add a comment | 3 Sub TEST() Dim value1 As String Dim value2 As String value1 = ThisWorkbook.Sheets(1).Range("A1").Value 'value from sheet1 value2 = ThisWorkbook.Sheets(2).Range("A1").Value 'value from sheet2 If value1 = value2 Then ThisWorkbook.Sheets(2).Range("L1").Value = value1 'or 2 End Sub

This will compare two sheets cells values and if they match place the value on sheet 2 in column L.

Share Improve this answer Follow answered Jul 16, 2016 at 8:16 James HefferJames Heffer 73211 gold badge66 silver badges1717 bronze badges 4

• How is this relevant to the question? (Except referencing a different sheet, which was already covered.) Also Value2 is a property of Range so I would recommend not using it as a variable name. – arcadeprecinct Commented Jul 16, 2016 at 13:27
• 3 First: Chill out, I was not trying to bash you. Your answer was up for review and I didn't flag it for deletion but asked you to clarify. I'm sorry if I offended you. Second: I reread the question and it says nothing about comparing values, and accessing different sheets is already covered in the accepted answer. Third: I know you declared value2 but Value2 is a property of Range. It is generally a good idea to not reuse names to avoid confusion (even though you won't have troubles with this one). – arcadeprecinct Commented Nov 15, 2016 at 8:05
• If you want we can continue this in the chat: chat.stackoverflow.com/rooms/111528/vba-lounge – arcadeprecinct Commented Nov 15, 2016 at 8:05
• 1 I'm most chilled dude, the reason I'm "comparing values", as you so nicely put it, is because, the question says, "I'm working with this values in sheet2 and i want to get the result in sheet1"... Now, I took "I want to get the result in sheet1", now to me that means, he wants a result that are on two sheets, but of course they should be the same? So that's my reason for validation. To ensure the result he's looking for on other sheets, matches the result he's looking for. It's simple validation bro... If I've understood this question wrong, then sorry... – James Heffer Commented Nov 16, 2016 at 10:11

Add a comment | 1 SomeVal=ActiveWorkbook.worksheets("Sheet2").cells(aRow,aCol).Value

did not work. However the following code only worked for me.

SomeVal = ThisWorkbook.Sheets(2).cells(aRow,aCol).Value Share Improve this answer Follow edited Jun 17, 2019 at 17:51 Zoe - Save the data dump♦ 28.1k2222 gold badges127127 silver badges158158 bronze badges answered May 11, 2015 at 6:33 RanonKahnRanonKahn 86211 gold badge1010 silver badges3434 bronze badges 6

• What is aRow and aCol? Are those numbers or aRow is e.g. "A"? – Trismegistos Commented May 14, 2016 at 9:44
• 'aRow' and 'aCol' are variables. – RanonKahn Commented Jun 24, 2016 at 23:39
• What do those variables contain does aRow contain number or string like "A"? – Trismegistos Commented Jun 26, 2016 at 11:38
• SomeVal = ThisWorkbook.Sheets(2).cells(1,"A").Value – RanonKahn Commented Nov 10, 2016 at 1:18
• SomeVal = ThisWorkbook.Sheets(2).cells("1","A").Value – RanonKahn Commented Nov 10, 2016 at 1:20

| Show 1 more comment 0

Try the worksheet activate command before you need data from the sheet:

objWorkbook.WorkSheets(1).Activate objWorkbook.WorkSheets(2).Activate Share Improve this answer Follow edited Jun 20, 2017 at 13:45 DimaSan 12.7k1515 gold badges6969 silver badges8080 bronze badges answered Jun 20, 2017 at 13:20 MikeMike 122 bronze badges Add a comment | 0

Maybe you can use the script i am using to retrieve a certain cell value from another sheet back to a specific sheet.

Sub reviewRow() Application.ScreenUpdating = False Results = MsgBox("Do you want to View selected row?", vbYesNo, "") If Results = vbYes And Range("C10") > 1 Then i = Range("C10") //this is where i put the row number that i want to retrieve or review that can be changed as needed Worksheets("Sheet1").Range("C6") = Worksheets("Sheet2").Range("C" & i) //sheet names can be changed as necessary End if Application.ScreenUpdating = True End Sub

You can make a form using this and personalize it as needed.

Share Improve this answer Follow answered Aug 23, 2019 at 4:50 Joni DeppJoni Depp 1133 bronze badges Add a comment | 0

Usually I use this code (into a VBA macro) for getting a cell's value from another cell's value from another sheet:

Range("Y3") = ActiveWorkbook.Worksheets("Reference").Range("X4")

The cell Y3 is into a sheet that I called it "Calculate" The cell X4 is into a sheet that I called it "Reference" The VBA macro has been run when the "Calculate" in active sheet.

Share Improve this answer Follow answered Jul 24, 2020 at 20:31 KasraKasra 10711 gold badge44 silver badges1414 bronze badges Add a comment | Highly active question. Earn 10 reputation (not counting the association bonus) in order to answer this question. The reputation requirement helps protect this question from spam and non-answer activity.

Not the answer you're looking for? Browse other questions tagged

• excel
• vba
• worksheet-function

or ask your own question.

• The Overflow Blog
• Why do developers love clean code but hate writing documentation?
• This developer tool is 40 years old: can it be improved?
• Featured on Meta
• The December 2024 Community Asks Sprint has been moved to March 2025 (and...
• Stack Overflow Jobs is expanding to more countries

Linked

-1 Excel Data Solutions 0 Color change when after merge files using macro [Excel] [Cristal Report XLS] -1 Get Values from Cells from other sheets 0 Assign the numeric value of a cell to an integer variable 0 Changing the format/layout of an excel worksheet

Related

0 Getting corresponding data from another sheet 1 Collect Cell Values from Column in Other Worksheet of the same WorkBook 1 Get value from another sheet 0 VBA Excel return value from other sheet 1 Referencing a cell from another worksheet in a function for VBA 0 How to get values from matching column in a different sheet 0 Retrieve a value from a different worksheet than the current one 0 Retrieving cell values from another sheet in the same excel workbook 0 Fetching data from a different worksheet in Excel VBA 1 How to pull data from another sheet in a user defined VBA function

Hot Network Questions

• How feasible would it be to "kill" the Sun by using blood?
• Why does ctldl.windowsupdate.com not use (valid) TLS?
• Why does a rod move faster when struck at the center rather than the edge, despite Newton's second law indicating the same acceleration?"
• I probably disallowed using the camera at some time in the past and now can't find a way to allow it again. How can I reenable it?
• Why is Jesus called Prince of Peace and not King of Peace considering he was also called Eternal Father?
• Can I omit 'мы' if the verb ends with '-ем'?
• Can a 4-d creature twist your right hand into a left hand without breaking it?
• Odds of hitting a star with a laser shone in a random direction
• Bolt of rear derailleur rounded out and broke off - repair wire thread
• Example of a strictly increasing continuous function differentiable almost everywhere that does not satisfy the Fundamental Theorem of Calculus
• Why does Trump want to raise/cancel the debt ceiling if DOGE will save trillions?
• Is 骰子 pronounced "shăi zi" or "tóu zi"?
• Will a 10-speed Tiagra shifter work with 9-speed sora drivetrain
• Merits of `cd && pwd` versus `dirname`
• Writing rhythm/slash notation on a single line staff?
• Why don't the Bene Gesserit retaliate against Vladimir Harkonnen for trying to kill Jessica and Paul?
• Best practice: How to correctly size the delimiters/fences of the following examples?
• Systemd service to start only after CIFS mount
• What keyboard shortcuts disable the keyboard?
• Why were my lead-acid batteries destroyed after operating them in parallel?
• Combining outer product of two lists
• Unusual drawing of amine hydrochloride
• Why recursive best first search is optimal if the heuristic function h(n) is admissible?
• Custom implementation of `std::unique_ptr<T>`

more hot questions Question feed Subscribe to RSS Question feed

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

lang-vb