I was able to reproduce your problem and can offer a solution, however
regrettably without being able to explain why it works. Replace the
section of your code that deals with the unprotecting, copying and
pasting, and re-protecting:
Worksheets("Computation").Range("compute").Copy
Sheets("PayDbase").Select
ActiveSheet.Unprotect Password:="tkh701"
Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial paste:=xlPasteValues,
Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False ' <<<<<<< the code shows error here
says
" PasteSpecial Range method class failed!"
Application.CutCopyMode = False
MsgBox "Posting Payroll Successful, you may print payslips
now!"
ActiveSheet.Protect Password:="tkh701"
with this code:
Sheets("PayDbase").Unprotect Password:="tkh701"
Dim targetCell As Range
Set targetCell = Worksheets("PayDbase").Range("A" &
Rows.Count).End(xlUp).Offset(1)
Dim sourceRange As Range
Set sourceRange = Worksheets("Computation").Range
("Compute")
sourceRange.Copy
targetCell.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, _
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
MsgBox "Posting Payroll Successful, you may print payslips
now!"
Sheets("PayDbase").Protect Password:="tkh701"
and give it another try.
Hope this helped,
Rolf
On Oct 27, 10:39 pm, OSAVentures Calamba <osav...@gmail.com> wrote:
> Hello Excel Gurus, Im not sure why this macro works and sometimes not.
> Could you please comment.
>
> Sub Find_First_before_ Copy_Paste()
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> End With
> Dim FindString As Integer
> Dim Rng As Range
> FindString = Sheets("Computation").Range("F7").Value
> If Trim(FindString) <> "" Then
> With Sheets("PayDbase").Range("E:E")
> Set Rng = .find(What:=FindString, _
> After:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not Rng Is Nothing Then
> MsgBox "THE PAYROLL PERIOD YOU ARE TYRING TO POST IS ALREADY
> CLOSED! "
> Else
> 'paste the entire ranged named to dbase worksheet
> ActiveSheet.Calculate
> Worksheets("Computation").Range("compute").Copy
> Sheets("PayDbase").Select
> ActiveSheet.Unprotect Password:="tkh701"
> Range("A65536").Select
> Selection.End(xlUp).Select
> ActiveCell.Offset(1, 0).Select
> Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False ' <<<<<<< the code shows error here says
> " PasteSpecial Range method class failed!"
> Application.CutCopyMode = False
> MsgBox "Posting Payroll Successful, you may print payslips now!"
> ActiveSheet.Protect Password:="tkh701"
> 'MsgBox "Nothing found"
> End If
> End With
> End If
> End Sub
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,500 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
