Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / July 2008

Tip: Looking for answers? Try searching our database.

Copying a values

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
dario90@gmail.com - 31 Jul 2008 12:19 GMT
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 - 31 Jul 2008 13:27 GMT
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
Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> I'm a newbie in this topic but tried to make sth like that:
>
[quoted text clipped - 16 lines]
> print label but it returns me error when refering to 2nd sheet. Can
> anyone help me with this?
dario90@gmail.com - 31 Jul 2008 13:52 GMT
> 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
[quoted text clipped - 50 lines]
> > print label but it returns me error when refering to 2nd sheet. Can
> > anyone help me with this?

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
Don Guillett - 31 Jul 2008 14:07 GMT
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

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

On 31 Lip, 14:27, "Don Guillett" <dguille...@austin.rr.com> wrote:
> You really should do this using data>filter>autofilter>non
> blanks>copy>paste
[quoted text clipped - 51 lines]
> > print label but it returns me error when refering to 2nd sheet. Can
> > anyone help me with this?

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@gmail.com - 31 Jul 2008 14:20 GMT
> Please TOP post for readability. Then try this.
>
[quoted text clipped - 77 lines]
> 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 - 31 Jul 2008 14:34 GMT
Perhaps you didn't understand my request to TOP post.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

On 31 Lip, 15:07, "Don Guillett" <dguille...@austin.rr.com> wrote:
> Please TOP post for readability. Then try this.
>
[quoted text clipped - 77 lines]
> 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@gmail.com - 31 Jul 2008 14:44 GMT
> Perhaps you didn't understand my request to TOP post.
>
[quoted text clipped - 92 lines]
> about items in C and want to print it on the same label - is another
> For... needed?

Maybe- what did you mean?
Sandy Mann - 31 Jul 2008 14:00 GMT
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

Signature

HTH

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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

> I'm a newbie in this topic but tried to make sth like that:
>
[quoted text clipped - 16 lines]
> print label but it returns me error when refering to 2nd sheet. Can
> anyone help me with this?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.