Excel macro vba, variable value not being used

Question

I'm using the below code, I'm scanning column B against a set of critera (Array) if the cell has one of the values, copy the row, then take the row to the employee name (Column A) and put the row on the next line down, should there be no sheet for the employee make a new one.

The code at the moment is getting to the second line (Same Employee name) and choosing to try and make a new sheet rather than add to the existing one.

This is causing an error as it cant make another sheet of the same name.

`Sub Sample()

Dim myarray

Dim wsInv As Worksheet, wsDes As Worksheet
Dim rngDes As Range, rngEmp As Range, cel As Range

Set wsInv = ThisWorkbook.Sheets("Inventory")
Set rngEmp = wsInv.Range("A2", wsInv.Range("A" & Rows.Count).End(xlUp).Address)

myarray = Array("CONSUMABLES", "FILTERS - BILLI TRIO", "FILTERS - ZIP GENERIC", _
    "GOODS", "HARDWARE FIXINGS", "LIGHTING - 50W DICHROIC", "LIGHTING - COMPACT BC/ES", _
    "LIGHTING - DICHROIC LAMP", "LIGHTING - FLURO", "LIGHTING - PLC LAMP 840/830", _
    "LIGHTING - PL-L", "LIGHTING - PULSE STARTER", "LIGHTING - STANDARD STARTER", _
    "LIGHTING - T5 FLURO", "NITROGEN CHARGE", "OXYGEN / ACETYLENE WELDING", _
    "R-134A", "R-22", "R-407C", "R-410A")

For Each cel In rngEmp
    If Not IsError(Application.Match(cel.Offset(0, 1).Value, myarray, 0)) Then
        On Error Resume Next
        Set wsDes = ThisWorkbook.Sheets(cel.Value)
        On Error GoTo 0
                'Error is here  vvvvv
        If wsDes Is Nothing Then Set wsDes = ThisWorkbook.Sheets.Add(after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
       'It should just move on but doesnt
        wsDes.Name = cel.Value
        cel(1 - (cel.Row - 1)).EntireRow.Copy wsDes.Range("A1")
        cel.EntireRow.Copy wsDes.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Set wsDes = Nothing
    End If
Next cel

End Sub`

Help? please!

In order for wsDes to have a value it is to equal

ThisWorkbook.Sheets(cel.Value)

When i highlight this line, it tells me

ThisWorkbook.Sheets(cel.Value) = <Subscript out of range>

This would give the nothing value to make a new sheet, thoughts??

When I'm duplicating rows beneath other rows, I always make my loop start at the bottom and go up to the top. That way, if you copy row 100 to row 101, the next row you're going to inspect isn't row 101 -- it's row 99! — BrettFromLA
– BrettFromLA, Commented Feb 5, 2014 at 0:39
So instead of make cel(1 - (cel.Row - 1)). into something that involes rows.count? — C Jones
– C Jones, Commented Feb 5, 2014 at 0:43
You can alway check if a sheet with that name exists in the workbook already, before you create a new one. (That might be a bad solution. But it's just a suggestion.) — BrettFromLA
– BrettFromLA, Commented Feb 5, 2014 at 0:43
Oh, right. Hmm. I'm sorry I couldn't be more helpful. I don't think I really understood your original question. (But I like VBA so I was trying to come up with something!!) — BrettFromLA
– BrettFromLA, Commented Feb 5, 2014 at 0:47

Tim Williams · Accepted Answer · 2014-02-05 00:44:25Z

1

You're always renaming the sheet and copying the headers, regardless of whether or not a new sheet has been created: you need to close off your if statement

If wsDes Is Nothing Then 
    Set wsDes = ThisWorkbook.Sheets.Add( _
                   after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsDes.Name = cel.Value 'now this only runs for new sheets...
    cel(1 - (cel.Row - 1)).EntireRow.Copy wsDes.Range("A1")
End If

answered Feb 5, 2014 at 0:44

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

C Jones Over a year ago

Hmmm, I thought this would do it however, not quite though it is still trying to make a new sheet for the second value

ARich Over a year ago

Does cel(1 - (cel.Row - 1)).EntireRow.Copy wsDes.Range("A1") need to be in the If statement?

Tim Williams Over a year ago

Any chance you might have extra spaces in the cell values? Try using Trim() before checking for the sheet. Set wsDes = ThisWorkbook.Sheets(Trim(cel.Value))

Tim Williams Over a year ago

Never trust data from other people to be what you think it is. Or even your own data! ;-)

Collectives™ on Stack Overflow

Excel macro vba, variable value not being used

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related