PDA

View Full Version : Solved: Macro Not Updating Worksheets



coliervile
03-16-2008, 07:25 AM
The following macro is suppose to look in the worksheet "Leave Request" column "A" and create a worksheet with records for each employee in filtered list. If a sheet already exists for a employee, it will be cleared, and the data will be extracted to that sheet. When I run the coding it returns an error colored in RED the error I'm getting is "Run-time error'1004': Method 'Name' of object_Worksheet' failed". The macro is controlled off of a button "Update Employee Records" on the worksheet "Dashboard". :dunno I believe the error is caused because there are no records in the worksheet "Leave Request"...can I avoid this error when there are no records????

Option Explicit
Sub ExtractEmp()
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range

Set rng = Range("Database")

With Worksheets("Leave Request")

.Range("A:E").Sort Key1:=.Range("A2"), _
Order1:=xlAscending, _
Header:=xlYes

'extract a list of employees
.Columns("A:A").Copy Destination:=.Range("L1")
.Columns("L:L").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=.Range("J1"), _
Unique:=True
r = .Cells(.Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
.Range("L1").Value = .Range("A1").Value

For Each c In .Range("J2:J" & r)

'add the employee name to the criteria area
.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then

Set wsNew = Sheets(c.Value)
wsNew.Cells.Clear
Else

Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
End If

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
.Cells.Copy
wsNew.Cells.PasteSpecial Paste:=xlPasteFormats
Next

.Columns("J:L").Delete

.Range("A:E").Sort Key1:=.Range("D2"), _
Order1:=xlAscending, _
Key2:=.Range("B2"), _
Order2:=xlAscending, _
Header:=xlYes
End With

End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

Bob Phillips
03-16-2008, 07:55 AM
The Leave Request worksheet is empty, and the loop is not well constructed in that it doesn't test for empty, so it tries to process J2, an empty cell.

coliervile
03-16-2008, 09:13 AM
Hello Bob how are you on this fine day...or it is here at least. How can you test for empty and then either continue the with the macro or exit sub???

Bob Phillips
03-16-2008, 09:18 AM
It's miserable here Charlie, not a good day at all.

I would check immediately after creating the last row variable r, and if it is not > 1 then don't do the loop.

Simon Lloyd
03-16-2008, 09:28 AM
Maybe this will point you in the right direction!

Sub Macro1()
Dim c As Range
Set c = Range("A1")
If c.Value = vbNullString Then
MsgBox "its empty"
Else
MsgBox "It's full"
End If
End Sub

coliervile
03-16-2008, 10:26 AM
Sorry for seeming a bit dense, but I can't get this to work or figure out where this is suppose to go?

Simon Lloyd
03-16-2008, 10:59 AM
I gave you that code so you could see the effects of checking a cell for an entry if A1 had an entry you would get the message box "It's Full" so you need to check the state of the range "c" before you start to manipulate your sheet or data, try replacing this section of code:

.Range("L2").Value = c.Value
If c.Value = vbNullString Then Goto Nxt
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then

Set wsNew = Sheets(c.Value)
wsNew.Cells.Clear
Else

Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
End If

rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=.Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
.Cells.Copy
wsNew.Cells.PasteSpecial Paste:=xlPasteFormats
Nxt:
Next

coliervile
03-16-2008, 12:50 PM
Simon thanks for your coding. I did look over the other coding and I did under stand it, but for the life of me I would not have been able to come up with what you did.

I'm not receiving and error when I run your code, but when an entry is made and is on the worksheet "Leave Request" and the information is updated everything seems to run ok. Here's where the problem lies... when information is deleted from the worksheet "Leave Request" and a worksheet already exists for a employee, it will be cleared, and the data will be extracted to that sheet. This doesn't happen the data remains on the employee worksheet (e.g. worksheet "AA"). Please take a look at the workbook... the button "Update Employee Record" that runs the macro is on the "Dashboard".

coliervile
03-16-2008, 12:52 PM
Here's the workbook...

coliervile
03-16-2008, 12:58 PM
Could something be added at the beginning of the macro that states If cell "A2"on worksheet "Leave Request" is blank exit sub otherwise run macro???

Norie
03-16-2008, 12:58 PM
Charlie

I've not responded recently to one of your threads.

But I just downloaded that workbook and it took about a minute to open.

Why do you have all these seemingly identical userforms and worksheets?

This really isn't a good approach.

I think it's really time that you sat down and had a bit of a rethink.:)

The way you are going about this is probably what's causing all your problems and the need to post so often.

coliervile
03-16-2008, 01:03 PM
Norie thanks for your input is there an easier way to do what I want to do with these userforms??? A reference would be useful.

Simon Lloyd
03-16-2008, 01:10 PM
Could something be added at the beginning of the macro that states If cell "A2"on worksheet "Leave Request" is blank exit sub otherwise run macro???If thats what you want then you just constructed it!!

If Sheets("Leave Request"),Range("A2")=vbNullString Then Exit Sub
no end if needed!

Norie
03-16-2008, 01:22 PM
Charlie

What I'm trying to say is you need to take a step back, take a rethink, forget about what you have now and think about another approach.

I mean having a different userform/worksheet for each person just doesn't make sense to me.

coliervile
03-16-2008, 01:28 PM
Thanks Norie I'll do that and see if I can come up with something a bit more simpler.

Simon I tried to place that coding at the beginning of the sub and ran into a compile error???

Simon Lloyd
03-16-2008, 01:29 PM
Charlie, Norie is right - remember you have to maintain this workbook! that said its not really our place to tell you how to design your project but give help and advice when needed, most of the folk on here (me excluded) use excel professionally and have a great wealth of experience - they took the knocks so you don't have to!

Simon Lloyd
03-16-2008, 01:31 PM
And the error was?

coliervile
03-16-2008, 01:36 PM
Sorry Simon "Brain Fried"... Complie error: Syntax Error. I'm taking a break and I'll be on later this evening. Simon Lloyd, Norie, and "XLD" thanks for your time and input thus far.

Simon Lloyd
03-16-2008, 01:44 PM
My fault! i used a cooma instead of a dot!

If Sheets("Leave Request").Range("A2")=vbNullString Then Exit Sub
this should work......but hey you should have noticed that, you gotta do a little investigative work yourself!

coliervile
03-16-2008, 06:21 PM
Thanks Simon for your help....have a good evening.