Copying a values

[dario90]

I'm a newbie in this topic but tried to make sth like that:

Sub CommandButton1_Click()
Columns("C:C").Select
For Each Cell in Selection
If Cell.Value <>"" Then
ActiveCell.Select
Selection.Copy
Sheet("Sheet2").Select
Range("E3:F4").Select
ActiveSheet.Paste
ActiveSheet.PrintOut
End If
Next Cell
End sub


I wanted to check values in column C in Sheet1 and if Cell isn't null
then copy this value to 2nd sheet which is a pattern of label and
print label but it returns me error when refering to 2nd sheet. Can
anyone help me with this?

 

[Don Guillett]

You really should do this using data>filter>autofilter>non blanks>copy>paste
Here is a recorded macro and then a cleaned up version without selections

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 7/31/2008 by Donald B. Guillett
'

'
Range("C1:C11").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
Columns("C:C").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B1").Select
ActiveSheet.Paste
End Sub

Sub copynonblanks()
lr = Cells(Rows.Count, "c").End(xlUp).Row
Range("C1:C" & lr).AutoFilter Field:=1, Criteria1:="<>"
Columns("C").Copy Sheets("Sheet2").Range("B1")
Range("C1:C" & lr).AutoFilter
End Sub

 

[dario90]

You really should do this using data>filter>autofilter>non blanks>copy>paste
Here is a recorded macro and then a cleaned up version without selections

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 7/31/2008 by Donald B. Guillett
'

'
    Range("C1:C11").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="<>"
    Columns("C:C").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B1").Select
    ActiveSheet.Paste
End Sub

Sub copynonblanks()
lr = Cells(Rows.Count, "c").End(xlUp).Row
Range("C1:C" & lr).AutoFilter Field:=1, Criteria1:="<>"
Columns("C").Copy Sheets("Sheet2").Range("B1")
Range("C1:C" & lr).AutoFilter
End Sub

I wrote it in button's code and returned me error in line:
Range("B1").Select ("Method Select from class Range didn't work").
What's more i want to make a loop using this reference- to print a
label for every item mentioned in Sheet1.Column C

 

[Sandy Mann]

The reason that your code objects to the sheet selection is that the correct
syntax is Sheets("Sheet2").select but as Don says you don't have to select
at all.

My reading of your request is slightly different in that I think that you
want to copy each entry in Column C to E3:F4 of sheet 2 and then print that
sheet. then copy the next value from Column C. If so then try:

Sub CommandButton1_Click()
Dim EndData As Long
Dim Labels As Range
Sheets("Sheet2").Select
Set Labels =
Sheets("Sheet1").Range("C:C").SpecialCells(xlCellTypeConstants, 23)
For Each cell In Labels
cell.Copy Destination:=Sheets("Sheet2").Range("E3:F4")
ActiveSheet.PrintOut
Next cell
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Don Guillett]

Please TOP post for readability. Then try this.

Sub CommandButton1_Click()
lr=cells(rows.count,"c").end(xlup).row
for each c in range("C1:C" & lr)
If C.Value <>"" Then
c.Copy Sheets("Sheet2").range("E3")
sheets("sheet2").PrintOut
End If
Next C
End sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

You really should do this using data>filter>autofilter>non
blanks>copy>paste
Here is a recorded macro and then a cleaned up version without selections

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 7/31/2008 by Donald B. Guillett
'

'
Range("C1:C11").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>"
Columns("C:C").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B1").Select
ActiveSheet.Paste
End Sub

Sub copynonblanks()
lr = Cells(Rows.Count, "c").End(xlUp).Row
Range("C1:C" & lr).AutoFilter Field:=1, Criteria1:="<>"
Columns("C").Copy Sheets("Sheet2").Range("B1")
Range("C1:C" & lr).AutoFilter
End Sub

I wrote it in button's code and returned me error in line:
Range("B1").Select ("Method Select from class Range didn't work").
What's more i want to make a loop using this reference- to print a
label for every item mentioned in Sheet1.Column C

 

[dario90]

Please TOP post for readability. Then try this.

Sub CommandButton1_Click()
lr=cells(rows.count,"c").end(xlup).row
 for each c in range("C1:C" & lr)
    If C.Value <>"" Then
      c.Copy Sheets("Sheet2").range("E3")
      sheets("sheet2").PrintOut
    End If
 Next C
End sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






I wrote it in button's code and returned me error in line:
Range("B1").Select ("Method Select from class Range didn't work").
What's more i want to make a loop using this reference- to print a
label for every item mentioned in Sheet1.Column C

Ok, great. Thank you. One more issue: when i have in column D notes
about items in C and want to print it on the same label - is another
For... needed?

 

[Don Guillett]

Perhaps you didn't understand my request to TOP post.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Please TOP post for readability. Then try this.

Sub CommandButton1_Click()
lr=cells(rows.count,"c").end(xlup).row
for each c in range("C1:C" & lr)
If C.Value <>"" Then
c.Copy Sheets("Sheet2").range("E3")
sheets("sheet2").PrintOut
End If
Next C
End sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






I wrote it in button's code and returned me error in line:
Range("B1").Select ("Method Select from class Range didn't work").
What's more i want to make a loop using this reference- to print a
label for every item mentioned in Sheet1.Column C

Ok, great. Thank you. One more issue: when i have in column D notes
about items in C and want to print it on the same label - is another
For... needed?

 

[dario90]

Perhaps you didn't understand my request to TOP post.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







Ok, great. Thank you. One more issue: when i have in column D notes
about items in C and want to print it on the same label - is another
For... needed?

Maybe- what did you mean?