Problem with For each

strucad · Aug 16th 2012

Hi everyone,
I have a code which copies a value from column A in one sheet, pastes it in a different sheet h30 and then prints the sheet see below. This use to work fine but now it prints 3 or 4 times even if I dont have any information in the column A of the first sheet. Anyone any ideas what is wrong, has the spreadsheet become corrupt?

Code

Sub c4000lc()
For Each c In ThisWorkbook.Worksheets("4numbers").UsedRange.Columns(1).Cells
        c.Copy Destination:=ThisWorkbook.Worksheets("C4000LC").Range("h30")
        ThisWorkbook.Worksheets("C4000LC").PrintOut Copies:=1, ActivePrinter:= _
        "GlobalMark Color & Cut (Copy 2) on COM1:"
    Next
    
End Sub

**pike** · Aug 17th 2012

Re: Problem with For each

Hello.
Usedrange maybe a range that is blank but did have data in at some time
you could select all the rows and delete them or possible use current region

Richie(UK) · Aug 17th 2012

Re: Problem with For each

I agree with pike, UsedRange can be a little unpredictable at times. How about just defining the range in column A that you want to use. Something like this perhaps:

Code

Sub c4000lc()
    Dim c As Range, rngData As Range
    Dim wsData As Worksheet, wsPrint As Worksheet
    
    Set wsData = ThisWorkbook.Worksheets("4numbers")
    Set wsPrint = ThisWorkbook.Worksheets("C4000LC")
    'the worksheets we are using
    
    With wsData
        Set rngData = .Range("A1:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
        'MsgBox rngData.Address
    End With
    'an alternative to UsedRange
    
    For Each c In rngData
        c.Copy Destination:=wsPrint.Range("h30")
        wsPrint.PrintOut Copies:=1, ActivePrinter:="GlobalMark Color & Cut (Copy 2) on COM1:"
    Next
    'loop through, as before
    
End Sub

Display More

Problem with For each

Participate now!

Share