+ Reply to Thread
Results 1 to 2 of 2

VBA Newbie needs some help with looping macro

Hybrid View

  1. #1
    Registered User
    Join Date
    03-06-2012
    Location
    Calgary, Alberta
    MS-Off Ver
    Excel 2010
    Posts
    48

    VBA Newbie needs some help with looping macro

    Hi everyone, hoping someone will be able to lend some knowledge.

    I'm trying to devise a looping macro to mass print based off a list of numbers pasted in by the user. Essentially I want to have a highlighted print area where the user can paste in the numbers click the button and go. I had set up something similar a few weeks back that worked fine (or at least no one has told me otherwise since I set it up).

    For this one I started by recording the copy/paste/print of the first number and than went into modify it into a loop that is below.

    Sub BulkPrint()
    '
    ' BulkPrint Macro
    '
    
    '
    For i = 50 To 250
    
        If ActiveSheet.Cells(i, 1).Value = "" Then Exit Sub
        Cells(i, 1).Select
        Selection.Copy
        Cells(2, 2).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("CFS Business Review").Select
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
        Sheets("Select CISID").Select
    
    Next i
    
    End Sub
    Now whenever I try to run the macro I get the error of "Compile error: cannot find project or library"

    Any thoughts??? I'm also trying to sift my way through the Excel built in help but I find the folks on here to be much more informative/helpful/understandable.

    Thanks in advance!

    ~James Cameron
    Last edited by CDNcameron; 05-08-2012 at 02:53 PM.

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: VBA Newbie needs some help with looping macro

    No problem here, you might want to check Tools>References in the VBIDE, uncheck any MISSING.

    Your code can be simplified

    Sub BulkPrint()
    Dim i As Long
        For i = 50 To 250
        
            With ActiveSheet
            
                If .Cells(i, 1).Value = "" Then Exit Sub
            
                .Cells(i, 1).Copy
                .Cells(2, 2).PasteSpecial Paste:=xlPasteValues
            End With
            
            Worksheets("CFS Business Review").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
        Next i
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1