Concatenating sheet name with string 1

[willydude]

I am trying to concatenate a string with the activesheet name to name a range (cell). This cells value from each individual sheet will then be copied to a summary sheet.

So if salesperson Joe has commissions on sheets Branch1, Branch2 and Branch3, the cell names for Joe’s commissions on these sheets will be JoeBranch1, JoeBranch2 and JoeBranch3, respectively.

I get an error message “That name is not valid” when the first concatenation should happen.

Sub NamingCommissionCellsForCopyingToSummarySheet()
Application.ScreenUpdating = False
Dim Wsheet As String
Wsheet = ActiveSheet.Name '<<this works

'For Each sheet In ActiveWorkbook.Worksheets
Range("A2").Select 'have to start at col A or Loop will not work
Do
With ActiveCell
 If ActiveCell.Font.Bold = True Then
  If Left(ActiveCell, 2) = 1 Then _
    ActiveCell.Offset(0, 11).Name = "Joe" & Wsheet  ‘<<Error here
  If Left(ActiveCell, 2) = 2 Then _
    ActiveCell.Offset(0, 11).Name = "HouseAccount"
  If Left(ActiveCell, 2) = 9 Then _
    ActiveCell.Offset(0, 11).Name = "HouseAccount"
  If Left(ActiveCell, 2) = 18 Then _
    ActiveCell.Offset(0, 11).Name = "JoeThomaCommissions"
 End If
End With
With ActiveCell 'need to fix this
      .Offset(1, 0).Select 'gotta have this or will get caught in loop
End With
'Stop
Loop Until ActiveCell = "Grand Total" 'Or _
     ActiveCell = "Pay This Amount>>"

'Stop
'Next sheet
Range("A1").Select
Application.ScreenUpdating = True
End Sub

TIA,

Bill

 

[AnotherHiggins]

That isn't how you name a cell (or range).

Turn on your Macro Recorder (Tools > Macro > Record new macro) and name a cell (or range).

Now observe the code that was created.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[willydude]

I'm stuck. I've tried the following:

With ActiveCell
 If ActiveCell.Font.Bold = True Then
  If Left(ActiveCell, 2) = 1 Then _
    ActiveCell.Offset(0, 11).Select
'ActiveWorkbook.Names.Add Name:="JoesComms", RefersToR1C1:= _
        "=& 'ActiveSheet.name'"
        ' "='Branch 3'!R13C12"
'   ActiveWorkbook.Names.Add Name:="JoesComms" & ActiveSheet.Name.Value
With ActiveCell
    ActiveWorkbook.Names.Add Name:="JoesComms" & ActiveSheet.Name
  End With

 

[SkipVought]

With ActiveCell
 If .Font.Bold = True Then
  If Left(.Value, 2) = 1 Then _
    .Offset(0, 11).Select
'ActiveWorkbook.Names.Add Name:="JoesComms", RefersTo:= _
        "=" & ActiveSheet.name & "!" & .address
    end if
   end if
  End With

Also check out Insert/Name/Create - Create names in...


Skip,

_[red][/red]

 

[willydude]

Skip, the following does name the cell, but it
does not add the Branch number to the name, i.e. "JoesCommsBranch1".

Plus, each time a new branch sheet is processed, the newest named cell overrides any other cell on a different sheet with the same name, i.e. "JoesComms" for Branch1 replaces "JoesComms" for "JoesComms" for Branch3 in the DefineName box.

In other words, the newest named cell is referenced. I need all cells to be referenceable by the names "JoesCommsBranch1", "JoesCommsBranch2", etc.

Thanks.

With ActiveCell
 If .Font.Bold = True Then
    If Left(.Value, 2) = 1 Then _
    .Offset(0, 11).Select
    ActiveWorkbook.Names.Add Name:="JoesComms", RefersTo:= _
        "=" & ActiveSheet.Name & "!" & .Address
   
    If Left(.Value, 2) = 2 Then _
    .Offset(0, 11).Select
    ActiveWorkbook.Names.Add Name:="BobsComms", RefersTo:= _
        "=" & ActiveSheet.Name & "!" & .Address
 
   If Left(.Value, 2) = 9 Then _
    .Offset(0, 11).Select
    ActiveWorkbook.Names.Add Name:="HouseAccount", RefersTo:= _
        "=" & ActiveSheet.Name & "!" & .Address
  
  If Left(.Value, 2) = 18 Then _
    .Offset(0, 11).Select
    ActiveWorkbook.Names.Add Name:="JoesCommsAI", RefersTo:= _
        "=" & ActiveSheet.Name & "!" & .Address
 End If
End With

 

[SkipVought]

???

"but it
does not add the Branch number to the name, i.e. "JoesCommsBranch1". "

I was only addressing the SYNTAX for the Add Names statement.

Concatenate to "JoesComms" whatever you like.

'sFindMeSomeString = [some find logic]
With ActiveCell
 If .Font.Bold = True Then
  If Left(.Value, 2) = 1 Then _
    .Offset(0, 11).Select
'ActiveWorkbook.Names.Add Name:="JoesComms" & sFindMeSomeString, RefersTo:= _
        "=" & ActiveSheet.name & "!" & .address
    end if
   end if
  End With

Skip,

_[red][/red]

 

[Gavona]

That isn't how you name a cell (or range).

I disagree. I regularly use that sort of code to name a range. It seems simpler than the names.Add method.

I wonder if the original problem was that

"Joe" & Wsheet

is an invalid name in that it contains invalid characters (or spaces). See how I have used Replace to eliminate the dollar signs.

Sub test()
ActiveCell.Offset(1, 1).Name = "named_from_" & Replace(ActiveCell.Address, "$", "_")
End Sub

Add a watch to "X"&Wsheet&"X" and see if there are extra leading or trailing spaces. Or simply use
Replace(Wsheet," ","_") in your code.


btw
1. you may also want to look at replacing your "if" statements with select case.
2. this works to redefine a range
Range("MyRangeName").currentregion.name = ("MyRangeName"))

Hope this helps.


Gavin

 

[willydude]

Gavin, very insightful. The problem was "Branch 6" instead of "Branch6" (or whatever) for the sheet names. Thanks.

Now I have 'the following that works OK as long as Joe has comms in branch6 (or other branch), but if he does not then there is an error message

If InStr(1, sheet.Name, "Branch6") Then 
Sheets("Summary of Commissions").Range("C11").Value = _
Sheets("Branch6").Range("JoeThomaBranch6")
Sheets("Summary of Commissions").Range("G11").Value = _
     Sheets("Branch6").Range("HouseAccountBranch6")
End If

tried the following. receive App defined or Object defined error msg because there are no comms for Joe in Branch6. have four sheets to look at for Joe and House comms.

If InStr(1, sheet.Name, "Branch6") Then 
    With ActiveSheet
''if Joe has comms from branch 6, copy amount to summary sheet if none, move 0. error on following line.

If Sheets("Branch6").Range("JoesCommsBranch6") Then
Sheets("Summary of Commissions").Range("C11").Value = _
Sheets("Branch6").Range("JoesCommsBranch6")
     Else
Sheets("Summary of Commissions").Range("C11").Value = 0
     End If
''if House has comms from branch 6, move amount to summary sheet if none, move 0

If Sheets("Branch6").Range("HouseAccountBranch6") Then
Sheets("Summary of Commissions").Range("C14").Value = _
Sheets("Branch6").Range("HouseAccountBranch6")  '.End(xlUp)
  Else
     Sheets("Summary of Commissions").Range("C14").Value = 0
  End If
  End With
End If

 

[willydude]

I need to clarify the above. If Joe does not have any comms in a particular branch, then the named range "JoesCommsBranch6" (or whatever)
will not exist on that particular sheet.

TIA.

 

[Gavona]

works OK as long as Joe has comms in branch6 (or other branch), but if he does not then there is an error message

So what do you want to happen in that circumstance. If you merely want to skip that line of code the try
On error resume next before the line of code and
On Error GoTo 0
after it.

Hope that helps you. Haven't realy looked at your second chunk of code.


Gavin

 

[willydude]

Awesome. With one exception.

If InStr(1, sheet.Name, "Branch6") Then 'Case sensitive
    On Error Resume Next
     Sheets("Summary of Commissions").Range("C11").Value = _
     Sheets("Branch6").Range("JoesCommsBranch6")
     On Error GoTo 0 'works except "C11" is empty, not zero
     On Error Resume Next
     Sheets("Summary of Commissions").Range("G11").Value = _
     Sheets("Branch6").Range("HouseAccountBranch6")
     On Error GoTo 0
End If

 

[Gavona]

Look up "on error" in help.
on error goto 0 just takes off error handling

Sheets("Branch6").Range("JoesCommsBranch6")
could be simplified to
Range("JoesCommsBranch6")
(unless you have named ranges at sheet level - which is most unusual)

How about this

On Error Resume Next
   Sheets("Summary of Commissions").Range("C11").Value = _
         Range("JoesCommsBranch6").value
   Sheets("Summary of Commissions").Range("G11").Value = _
         Range("HouseAccountBranch6").value
On Error GoTo 0
End If

If case sensitivity is an issue then convert the string you are testing to uppercase:
If InStr(1, UCase(sheet.Name), "BRANCH6")

I am not really sure what you are trying to do.




Gavin