CODEWITHSUNDEEP
vbaexcel
Crecket
Crecket

Reputation: 718

Get sum from other sheet based on name in the same row

Basically what I need to do is get the value from a specific cell from a different worksheet. The tricky part is that the name of the sheet that it has to access is displayed in a column in the same sheet.

Here is the image So the status column has to get the number from the other sheet. And the other sheet has to be the name from the "sheetname" column in the same row. If there is no sheet yet it should just stay empty.

Current code I have is this. All this does is create/open the other sheet so far.

Sub CreateNewSheet()
sheet_name_to_create = Range("A" & (ActiveCell.Row)).Value
' Check if filename exists, if false create new else make it active
For rep = 1 To (Worksheets.Count)
    If LCase(Sheets(rep).Name) = LCase(sheet_name_to_create) Then
       ActiveWorkbook.Sheets(sheet_name_to_create).Activate
       Exit Sub
    End If
Next
    Sheets("TEMPLATE").Copy after:=Sheets(Sheets.Count)
    Sheets(ActiveSheet.Name).Name = sheet_name_to_create
End Sub

Its a dutch project so ignore the words at the top btw :p

Upvotes: 0

Views: 556

Answers (2)

Sobigen
Sobigen

Reputation: 2169

From Rory's comment it looks like if you put this formula in cell B1, you would just need to know the cell in the other worksheet (A1 in the example).

=INDIRECT(A1 & "!A1")

Upvotes: 1

Dubison
Dubison

Reputation: 768

I am not sure if I get you correct but below example will use Column A data as worksheet name and return cell A1 value in that target sheet to Column B same row.

Sub findNOW()
Dim lastRow As Long
Dim WS As Worksheet

Set WS = ActiveWorkbook.Sheets("Main1")
lastRow = WS.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To lastRow
        On Error Resume Next
        WS.Range("B" & i).Value = ThisWorkbook.Sheets(WS.Range("A" & i).Value).Range("A1").Value
    Next
End Sub

Upvotes: 1

Related Questions