@Rolf Thank you very much for your help. You are sent by angels.
I read your code and tried to self understand the logic because im just a
beginner. I made some remarks. I also pasted here my revised code which i
adopted from many sites to suit my actual situation. I rename
Post_to_summary TO Post_to_Dbase ( second before your email came in). Is it
better if you can comment your logic flow for beginners like me.
Sub Post_to_Dbase()
'suppressing screen
With Application
.ScreenUpdating = False
.DisplayAlerts = False
End With
'declaring variable
Dim PayPeriod As Integer
Dim Rng As Range
'in case of error what ever reason
On Error GoTo Err_Execute
'Retrieve date value to search for
PayPeriod = Sheets("Computation").Range("B7").Value
'select worksheet to paste append from if the variable is exiting
If PayPeriod <> "" Then
With Sheets("PayDbase").Range("B:B")
Set Rng = .Find(What:=PayPeriod, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
'paste the entire ranged named to dbase worksheet
ActiveSheet.Calculate
Worksheets("Computation").Range("compute").Copy
Sheets("PayDbase").Select
Range("A65536").Select
Selection.End(xlUp).Select
'one empty row down to paste the values
ActiveCell(1, 0).Select
Worksheets("PayDbase").Range("a6").PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
MsgBox "Payroll closed and posted, you may print payslips now!"
Else
MsgBox "Payroll Period is already posted!"
End If
End With
With Application
.ScreenUpdating = True
.DisplayAlerts = True
End With
Err_Execute:
MsgBox "An error occurred."
End Sub
On Wed, Oct 28, 2009 at 4:00 AM, RolfJ <r...@pacificsound.us> wrote:
>
> I took another look at your workbook and think I have a reasonable
> solution for your Post_to_Summary routine:
>
> Sub Post_to_Summary()
>
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> End With
>
> Dim nPeriod As String ' my period is an integer coz of some math
> operations somewhere
> nPeriod = Range("Compute").Cells(1, 5).Value
>
> Dim targetColumn As Range
> 'this code pasting the range "compute" already? ' compute is dynamic
> named ranged already?"*
> *
* *
> Set targetColumn = Worksheets("DBase").Columns("E")
> Dim targetCell As Range
>
> Application.CutCopyMode = False
>
> Set targetCell = targetColumn.Find(What:=nPeriod,
> After:=targetColumn.Cells(1, 1), LookIn:=xlFormulas, LookAt _
> :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
> MatchCase:= _
> False, SearchFormat:=False)
>
> If targetCell Is Nothing Then
> Set targetCell = Worksheets("DBase").Range("E" &
> Rows.Count).End(xlUp).Offset(1, -4)
> Dim sourceRange As Range
> Set sourceRange = Worksheets("Computation").Range("Compute")
> sourceRange.Copy
> targetCell.PasteSpecial Paste:=xlPasteValues
> End If
>
> Application.CutCopyMode = False
> With Application
> .ScreenUpdating = True
> .DisplayAlerts = True
> End With
>
> End Sub
>
> I am still a bit fuzzy on what you are trying to accomplish with your
> Prints_All_slips routine, but maybe this code does what you are
> looking for (if not it should be reasonably straightforward to make
> any modifictions (e.g. regarding the formatting which I omitted) you
> might need):
>
> " I want to print all the slips for the current period. *My workbook is a
payroll template
workbook*. The flow goes as follows. The structure of computation and
dbase worksheet is the same.
start->Compute payment for the current period -> then post to dbase
worksheet-> print payslip based on posted data on dbase worksheet. This is
the routine. Thanks for indulgence.
Sub Prints_All_slips()
>
> With Application
> .ScreenUpdating = False
> .DisplayAlerts = False
> End With
>
> If MsgBox(" Printer Properly Set-up?", vbYesNo + vbQuestion) =
> vbNo Then
> Exit Sub
> Else
> Dim iRow As Long
> Dim nSlip As Integer
> nSlip = 1
> Dim idNo As String
> Dim nPeriod As String
> Do
> idNo = Worksheets("Computation").Range("B7").Offset
> (iRow).Value
> Worksheets("SLIPs").Range("D" & 4 + (nSlip - 1) *
> SLIP_ROWSPACING).Offset(iRow).Value = idNo
> nPeriod = Worksheets("Computation").Range("B7").Offset
> (iRow, 4).Value
> Worksheets("SLIPs").Range("M" & 6 + (nSlip - 1) *
> SLIP_ROWSPACING).Offset(iRow).Value = nPeriod
> If Worksheets("Computation").Range("B7").Offset(iRow +
> 1).Value = "" Then Exit Do
> iRow = iRow + 1
> nSlip = nSlip + 1
> Loop
> End If
>
> With Application
> .ScreenUpdating = True
> .DisplayAlerts = True
> End With
>
> End Sub
>
> Hope this helped,
> Rolf
>
>
>
>
> On Oct 27, 3:44 am, OSAVentures Calamba <osav...@gmail.com> wrote:
> > @Rolf Thank you very much. I attached my reduced sheet with explanation.
> > ...*you say 'it
> > stops'. [macro stops running or break] Also, what to you mean to indicate
> > with the -<<<< debugs here flag?* *[when the macro breaks it points to
> that
> > line.]*
> >
> > I've 'painfully revised the code. the updated one is below. quite
> > complicated for a beginner like me.
> >
> > below is my erratic code to paste the entire named ranged to my dbase
> > worksheet. I want to insert a code where it should stop posting if the
> > values is found in the dbase already. [ explained in the attached
> worksheet]
> >
> > this one also overwrites data in the dbase worksheet. which should not
> be.
> > it should append only on the next row containing data.
> >
> > Sub Post_to_Summary()
> > With Application
> > .ScreenUpdating = False
> > .DisplayAlerts = False
> > End With
> > Worksheets("Computation").Range("compute").Copy
> > Worksheets("PayDbase").Range("a6").PasteSpecial Paste:=xlPasteValues,
> > Operation:=xlNone, SkipBlanks _
> > :=False, Transpose:=False
> > Application.CutCopyMode = False
> > 'Range("compute").Select
> > 'Selection.Copy
> > 'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> > SkipBlanks _
> > :=False, Transpose:=False
> > With Application
> > .ScreenUpdating = True
> > .DisplayAlerts = True
> > End With
> > End Sub
> >
> > Below is my code ( full of error ) to copy_paste_print as explained in
> the
> > [attached worksheet]. I just get some code from macro recording.
> >
> > Sub Prints_All_slips()
> > With Application
> > .ScreenUpdating = False
> > .DisplayAlerts = False
> > End With
> > If MsgBox(" Printer Properly Set-up?", vbYesNo + vbQuestion) = vbNo
> Then
> > Exit Sub
> > Else
> > ' Select cell b7, first line of data computation sheet.
> > 'Do Until IsEmpty(ActiveCell)
> > Sheets("Computation").Select
> > Range("B7").Select
> > Selection.Copy
> > 'ActiveCell.Offset(1, 0).Select
> > Sheets("PAYSLIP").Select
> > Range("D4").Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > Do Until IsEmpty(ActiveCell)
> > Sheets("Computation").Select
> > ActiveCell.Offset(1, 0).Select
> > Selection.Copy
> > Sheets("PAYSLIP").Select
> > Range("D41").Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > 'Print_All_Pay
> > Range("B1:N68").Select
> > Selection.PrintOut Copies:=1
> > 'Print_All_Pay
> > Sheets("Computation").Select
> > ActiveCell.Offset(1, 0).Select
> > Selection.Copy
> > Sheets("PAYSLIP").Select
> > Range("D4").Select
> > Selection.PasteSpecial Paste:=xlPasteValues
> > Application.CutCopyMode = False
> > With Selection
> > .HorizontalAlignment = xlGeneral
> > .VerticalAlignment = xlBottom
> > End With
> > Loop
> > With Application
> > .ScreenUpdating = True
> > .DisplayAlerts = True
> > End With
> > End If
> > End Sub
> >
> >
> >
> > On Tue, Oct 27, 2009 at 12:15 PM, RolfJ <r...@pacificsound.us> wrote:
> >
> > > I am having difficulties following what you would like to do. In
> > > particular it is not clear to me what you mean when you say 'it
> > > stops'. Also, what to you mean to indicate with the -<<<< debugs here
> > > flag?
> >
> > > Please post or e-mail example worksheets and I'll take a closer look.
> >
> > > On Oct 26, 6:28 pm, OSAVentures Calamba <osav...@gmail.com> wrote:
> > > > Dear Macro Experts,
> >
> > > > I'm just starting to write a macro. What I'm trying to do is I want
> to
> > > copy
> > > > a value from 'computation' sheet.column B and posting it as values in
> > > 'Slip'
> > > > range "D4" and do that while sheet.columnB is not empty. Below is
> the
> > > code
> > > > i made but stops.
> >
> > > > Sub copyIDtopay()
> > > > With Application
> > > > .ScreenUpdating = False
> > > > .DisplayAlerts = False
> > > > End With
> > > > ' Select cell b7, first line of data computation sheet.
> > > > Do Until IsEmpty(ActiveCell)
> > > > Worksheets("Computation").Range("B7").Copy -<<<<<debugs
> here
> > > > Worksheets("SLIP").Range("D4").PasteSpecial
> Paste:=xlPasteValues
> > > > Sheets("Computation").Select
> > > > ActiveCell.Offset(1, 0).Select
> > > > Selection.Copy
> > > > Sheets("SLIP").Select
> > > > Range("D41").Select
> > > > ActiveSheet.Paste
> > > > Application.CutCopyMode = False
> > > > Loop
> > > > .ScreenUpdating = False
> > > > .DisplayAlerts = False
> >
> > > > End Sub
> >
> > > > Please help me.
> >
> >
> >
> > Ptemplate.xls
> > 102KViewDownload- Hide quoted text -
> >
> > - Show quoted text -
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
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
-~----------~----~----~----~------~----~------~--~---
