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
nPeriod = Range("Compute").Cells(1, 5).Value
Dim targetColumn As Range
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):
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
-~----------~----~----~----~------~----~------~--~---
