Ooops! I forgot to mention that I introduced another constant for the
'Period' column (PERIOD_COLUMN) in your Computation worksheet. Please
add this declaration at the top of your VBA module:
Const PERIOD_COLUMN As String = "E"
Doing this is not really required, but it typically simplifies code
management when later decide to insert a column in your worksheet.
Rather than having to thieve through your macros to find out where you
used "E", you could just change the assignment of the constant in one
place. Just a suggestion.
You will of course notice that I didn't follow my own guideline when
referring to column E in your 'PayDbase' worksheet...
Best wishes,
Rolf
On Oct 27, 8:54 pm, OSAVentures Calamba <osav...@gmail.com> wrote:
> @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
>
> ...
>
> read more »- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -- 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
-~----------~----~----~----~------~----~------~--~---
