Excel formula/vba not picking up tab name in indirect formula

[Dancarro]

Hi,

I have a vba code (see below) which creates a tab worksheet and is named as per the text entered in cell 'G2'; and enters the same name into another sheet which is called 'OPERATION'. So the vba works well, now the problem I have is using indirect formula to recall the tab name in a cell in wk 'OPERATION'

=SUMIF(INDIRECT(A3&"!I&I"),$K$1,INDIRECT(A3&"!K:K")
Cell A3= name which is the same as per the tab worksheet that was created by the vba

The formula works well when there is no spaces inbetween the words, but the formula doesn't work when there are spaces. Any advise on this?

Sub Copyrenameworksheet()

Dim ws As Worksheet
Set wh = Worksheets(ActiveSheet.Name)
ActiveSheet.Copy After:=Worksheets(Sheets.Count)
If wh.Range("G2").Value <> "" Then
ActiveSheet.Name = wh.Range("G2").Value
End If
'wh.Activate

Dim src As Worksheet
Dim dst As Worksheet
Dim rw As Long

Application.ScreenUpdating = False

Set src = Sheets("FORM")
Set dst = Sheets("OPERATION")

rw = dst.Cells(Rows.Count, "A").End(xlUp).Row + 1

dst.Cells(rw, "A") = src.Range("G2")
dst.Cells(rw, "B") = src.Range("G3")

Application.ScreenUpdating = True

Range("G2:G3").ClearContents
Range("K6:K67").ClearContents


End Sub

Kind Regards,
Dan

 

[Joe4]

If you do a simple test, you will be able to see what you need to do.

Take a simple workbook that has "Sheet1" and insert a new sheet so now it has a "Sheet2".
Then, from any cell on Sheet1, type in an equal sign, then go over to Sheet2, select cell A1 and hit enter.
Now, look at the formula it just created on Sheet1:
=Sheet2!A1

Rename "Sheet2" to "Sheet2 New", and then go back and look at your formula now, and notice how it changed:
='Sheet2 New'!A1
Notice the single quotes around the Sheet name.

So, that is the key; you need single quotes around the sheet name. So you need to incorporate that into your code.
Note: You might not always know if the sheet name is going to have spaces or not. That is not a problem. If you put single quotes around a name without spaces, it should still work.

 

[Dancarro]

Hi Joe,

The issue is that I need the "" as I am using A3&"!.."; the single quotes doesn't work as it gives me an error.

 

[Joe4]

OK, I misspoke earlier. I think it is the formula, not the VBA code, that needs updating.

Try:
=SUMIF(INDIRECT("'"&A3&"'!I&I"),$K$1,INDIRECT("'"&A3&"'!K:K")
(I recommend copying and pasting my formula directly instead of trying to type it -- it is very easy to mess up the different single and double-quotes otherwise!).