Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 11th, 2005, 02:55 PM
Authorized User
 
Join Date: Nov 2005
Location: phoenix, AZ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default easy pasting problem

I am writing some vb code to help my friends dad with excel and I'm having a problem with one part. Basically my code checks if the value in certaing column's is = 0 or > 1 for each row then if it's true it select the entire row, copies it and is supposed to paste it on the next sheet. However, I can't figure out the selection and pasting. I have the copying part down but need help with the rest. my code goes something like this:

for i=2 to 50000
    cola=cells(i,1).value
    colu=cells(i,17).value
    colq=cells(i,21).value
    if (cola > 0 and colu=0 and colq=0) then
     'the copying is done somehow but since im not at the computer with the code this part might be wrong
     entirerow.copy
     sheets("sheet2").activate
     'heres where i need help
      cells(j,1).select
      selection.paste
      j=j+1
      sheets("sheet2").activate
     end if
next i

I have every other part done so this all I need. any help would be greatly appreciated
Reply With Quote
  #2 (permalink)  
Old November 14th, 2005, 12:56 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to shattered Send a message via Yahoo to shattered
Default

Sub CopyData()
Application.ScreenUpdating = False
Dim dLoop As Double
Dim dRow As Double
' Set starting value for row counter on sheet 2, adjust as required
dRow = 1
For dLoop = 2 To 50000
    If Cells(dLoop, 1) > 0 And Cells(dLoop, 17) = 0 And Cells(dLoop, 21) = 0 Then
        ' Copy Data
        Rows(dLoop & ":" & dLoop).Select
        Selection.Copy
        ' Paste Dats
        Sheets("Sheet2").Select
        Range("A" & dRow).Select
        ActiveSheet.Paste
        ' Return to original sheet
        Sheets("Sheet1").Select
        ' Increment Row counter on second sheet
        dRow = dRow + 1
    End If
Next iLoop
Application.ScreenUpdating = True
End Sub

Reply With Quote
  #3 (permalink)  
Old November 16th, 2005, 06:04 PM
Authorized User
 
Join Date: Nov 2005
Location: phoenix, AZ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks!
Reply With Quote
  #4 (permalink)  
Old November 17th, 2005, 04:01 AM
Friend of Wrox
 
Join Date: Jan 2005
Location: Bournemouth, Dorset, United Kingdom.
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Taking this on a bit further, another way could be to insert the copied row into row 2 of sheet 2 with all other rows being pushed down one line, 6 of one half a dozen of another?

Sub CopyData()
Application.ScreenUpdating = False
Dim dLoop As Double
For dLoop = 2 To 50000
    If Cells(dLoop, 1) > 0 And Cells(dLoop, 17) = 0 And Cells(dLoop, 21) = 0 Then
        ' Copy Data
        Rows(dLoop & ":" & dLoop).EntireRow.Copy
' Selection.Copy
        Sheets("Sheet2").Select
        Range("2:2").Insert Shift:=xlDown
        ' Return to original sheet
        Sheets("Sheet1").Select
    End If
Next iLoop
Application.ScreenUpdating = True
End Sub


cheers

Matt

Reply With Quote
  #5 (permalink)  
Old November 17th, 2005, 06:36 PM
Authorized User
 
Join Date: Nov 2005
Location: phoenix, AZ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

when it gets to
        Range("A" & dRow).Select
it says "select method of range class failed
Reply With Quote
  #6 (permalink)  
Old November 18th, 2005, 05:45 AM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to shattered Send a message via Yahoo to shattered
Default

hmm, the code works fine for me (well, with one small change - change Next iLoop to read Next dLoop) but that wouldn't cause your problem..

Can you put a breakpoint in the code at that point and then check the value of the following elements in the immediate window (type as shown)

?dRow
?vartype(dRow)
?dLoop
?vartype(dLoop)
?activesheet.name


Reply With Quote
  #7 (permalink)  
Old November 22nd, 2005, 12:28 PM
Authorized User
 
Join Date: Nov 2005
Location: phoenix, AZ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I don't quite understand what you are asking in the last part, and just so you know when i type it in t changes by itself to Print (everything) instead of ?(everything)
Reply With Quote
  #8 (permalink)  
Old November 22nd, 2005, 12:46 PM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to shattered Send a message via Yahoo to shattered
Default

sorry, it changed to print beacuse you typed it in the code window, should have said I wanted you to type in the immediate window.

Click to the left of the line with the problem to set a breakpoint (the line will turn brown and a brown circle will appear in the left margin).

Now run the code as normal, it will stop when it reaches that point.
Open the immediate window if not already open (Ctrl + G)
Now type each of those elements and see what results you get.

Reply With Quote
  #9 (permalink)  
Old November 23rd, 2005, 11:25 AM
Authorized User
 
Join Date: Nov 2005
Location: phoenix, AZ, USA.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

this is what i got
?dRow
 1
?vartype(dRow)
 5
?dLoop
 2
?vartype(dLoop)
 5
?activesheet.name
Sheet2
Reply With Quote
  #10 (permalink)  
Old November 23rd, 2005, 11:38 AM
Authorized User
 
Join Date: Oct 2004
Location: , , .
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to shattered Send a message via Yahoo to shattered
Default

ok, so it appears to die on the first pass..

Is either sheet protected?
Are the cells you are copying from or to merged at all?

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
pasting data from clipboard rahesh Visual Basic 2005 Basics 0 May 23rd, 2008 12:40 PM
Pasting into spreadsheets IainAL VB How-To 0 April 23rd, 2007 04:33 AM
Copy and Pasting jilly Beginning VB 6 1 March 8th, 2006 09:46 AM
Copieng and pasting with VBA vorax187 Access VBA 2 October 13th, 2005 11:58 AM
Very easy problem for an expert Nicky2k ASP.NET 2.0 Basics 3 April 21st, 2005 07:18 AM



All times are GMT -4. The time now is 05:32 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.