macro works separately but not together...

[Gbiwan]

Hi!

I've compiled the following that works separately but now when I put the
entire thing together... I get an error message... being good at copy and
paste but not necessarily good at the "whatfors" I'm not sure where to
look...

Also, I wasn't sure where the error occurred so once again I'm not being
brief and pasted the entire compilation... Sorry... (hopefully you'll see
all your good work here and find my error with your wizardry...

Sub Mail_Activesheet()
Range("A1").Select
Sheets("PAYROLL SHEET").Select
Range("A4").Select
ActiveSheet.Unprotect Password:="MONEY"
Sheets("PAYROLL SHEET").Select
Range("A4").Select
Dim ans
For i = 9 To 133
If Not IsEmpty(Cells(i, "A").Value) And IsEmpty(Cells(i, "u")) Then
ans = ""
Do While ans = ""
ans = InputBox("Cell " & Cells(i, "V").Address(False, False)
& " missing Total hours, please enter", "Data Completion")

Loop
End If
Next i
Range("a1").Select
ActiveWorkbook.Save
Dim rng As Range
Dim rng1 As Range
Set rng = Worksheets("sheet3").Range("A1").CurrentRegion
rng.AutoFilter Field:=1, Criteria1:="<>"
Set rng1 = rng.Offset(1, 0). _
Resize(rng.Rows.Count - 1)
rng1.Copy
Sheets("HO Payroll").Range("A1") _
.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
sKIPBLANKS:=False, _
Transpose:=False
Application.CutCopyMode = False
rng.AutoFilter
Range("a4").Select
Sheets("Sheet3").Select
Range("A1").Select
Dim strDate As String
Sheets(Array("Payroll Sheet", "HO PAYROLL")).Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone,
sKIPBLANKS:=False, Transpose:=False
Cells(1).Select
Application.CutCopyMode = False
Sheets("PAYROLL SHEET").Select
Range("ab1:dq775").Delete
Range("a1").Select
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm")
MsgBox "Remember to obtain the workbook password" & Chr(13) & _
" from the Payroll Department."
ActiveSheet.SaveAs "Dummy'S " & Range("R4") & " " & "Payroll" & " " &
strDate, FileFormat:=xlNormal, Password:="MONKEY", <-- error here
WriteResPassword:="MONKEY", ReadOnlyRecommended:=False,
CreateBackup:=False
Range("A1").Select
ActiveWorkbook.ChangeFileAccess xlReadOnly
ActiveWorkbook.Close False
Sheets("PAYROLL SHEET").Select
ActiveSheet.Protect Password:="MONKEY", DrawingObjects:=True,
CONTENTS:=True, Scenarios:=True
Range("a1").Select
End Sub


Any ideas?
Sorry about the length... thanks in advance for your time and assistance!

 

[Bob Kilmer]

I suggest that you select your project in the Project Explorer, go to the
Debug menu, select "Compile VBAProject" and fix any errors if any are
announced. Try to run the project. If it does not run, where does it stop
and what error message displays? It is sometimes helpful to use the F8 key
(or Step Into on the Debug menu or Debug toolbar) to step into and thru the
code line by line, checking variable values as you go.

If you post an exact error message and the line where the code fails, it
will be easier for someone to help.

Bob

 

[Gbiwan]

OK here is the info you suggested to post...

ActiveSheet.SaveAs "Dummy'S " & Range("R4") & " " & "Payroll" & " " &
strDate, FileFormat:=xlNormal, Password:="MONKEY",
WriteResPassword:="MONKEY", ReadOnlyRecommended:=False, CreateBackup:=False

The Error is a
Run-time error '13'
Type mismatch

The line above is one line only as well. Does this help?

Thanks for looking at this!

Greg

 

[Bob Kilmer]

(a "wrapped" version of the troublesome line)

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY", _
WriteResPassword:="MONKEY", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

"Type mismatch" usually means the data type (Integer, Boolean, String, etc.)
of a variable and the value being assigned to it are incompatible. It is not
obvious to me here what is wrong. Range("R4") is technically a Range object
not a string but its default property will return a String. Better to use
Range("R4").Text or Range("R4").Value.

I would test the staement incrementally:
Does this raise an error or not? If so, fix it.
Debug.Print "Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate

Try each of these in turn until something doesn't work.
ActiveSheet.SaveAs "foo"

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY"

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY", _
WriteResPassword:="MONKEY"

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY", _
WriteResPassword:="MONKEY", _
ReadOnlyRecommended:=False

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY", _
WriteResPassword:="MONKEY", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Bob

 

[Gbiwan]

THANKS!
That worked!

Greg

(a "wrapped" version of the troublesome line)

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY", _
WriteResPassword:="MONKEY", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

"Type mismatch" usually means the data type (Integer, Boolean, String, etc.)
of a variable and the value being assigned to it are incompatible. It is not
obvious to me here what is wrong. Range("R4") is technically a Range object
not a string but its default property will return a String. Better to use
Range("R4").Text or Range("R4").Value.

I would test the staement incrementally:
Does this raise an error or not? If so, fix it.
Debug.Print "Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate

Try each of these in turn until something doesn't work.
ActiveSheet.SaveAs "foo"

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY"

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY", _
WriteResPassword:="MONKEY"

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY", _
WriteResPassword:="MONKEY", _
ReadOnlyRecommended:=False

ActiveSheet.SaveAs _
"Dummy'S " & Range("R4") & " " & "Payroll" & " " & strDate, _
FileFormat:=xlNormal, _
Password:="MONKEY", _
WriteResPassword:="MONKEY", _
ReadOnlyRecommended:=False, _
CreateBackup:=False

Bob



"