Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > macro works separately but not together...
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default macro works separately but not together...
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!


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 71
Default macro works separately but not together...
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" wrote in message
...
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!




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default macro works separately but not together...
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" wrote in message
...
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" wrote in message
...
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!






Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 71
Default macro works separately but not together...
(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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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!








Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default macro works separately but not together...
THANKS!
That worked!

Greg

"Bob Kilmer" wrote in message
...
(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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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!










Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
macro works - very quick driller Excel Discussion (Misc queries) 6 October 10th 09 06:14 PM
Macro works Macro does not work Wanna Learn Excel Discussion (Misc queries) 4 March 24th 08 12:51 PM
Macro do works in excel... driller Excel Worksheet Functions 7 July 12th 07 02:56 PM
Email Macro only sometime works? PaulW Excel Discussion (Misc queries) 0 August 15th 06 10:22 AM
macro works in .xlt but not .xls BrianG[_3_] Excel Programming 6 September 18th 03 10:13 PM


All times are GMT +1. The time now is 12:31 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement