Advanfill từ giữa 2 sheet vba

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?

asked Nov 10, 2010 at 20:29

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)

answered Nov 10, 2010 at 20:35

Doc BrownDoc Brown

19.3k6 gold badges51 silver badges87 bronze badges

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...

answered Nov 10, 2010 at 20:35

jpinto3912jpinto3912

1,4652 gold badges11 silver badges19 bronze badges

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.

answered Jul 16, 2016 at 8:16

James HefferJames Heffer

6461 gold badge6 silver badges17 bronze badges

4

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

answered May 11, 2015 at 6:33

RanonKahnRanonKahn

7878 silver badges30 bronze badges

6

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

objWorkbook.WorkSheets(1).Activate
objWorkbook.WorkSheets(2).Activate

DimaSan

11.7k11 gold badges65 silver badges74 bronze badges

answered Jun 20, 2017 at 13:20

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.

answered Aug 23, 2019 at 4:50

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.

answered Jul 24, 2020 at 20:31

KasraKasra

1054 silver badges13 bronze badges