Wrox Programmer Forums
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 11th, 2005, 02:55 PM
Authorized User
 
Join Date: Nov 2005
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
 
Old November 14th, 2005, 12:56 PM
Authorized User
 
Join Date: Oct 2004
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

 
Old November 16th, 2005, 06:04 PM
Authorized User
 
Join Date: Nov 2005
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks!
 
Old November 17th, 2005, 04:01 AM
Friend of Wrox
 
Join Date: Jan 2005
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

 
Old November 17th, 2005, 06:36 PM
Authorized User
 
Join Date: Nov 2005
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
 
Old November 18th, 2005, 05:45 AM
Authorized User
 
Join Date: Oct 2004
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


 
Old November 22nd, 2005, 12:28 PM
Authorized User
 
Join Date: Nov 2005
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)
 
Old November 22nd, 2005, 12:46 PM
Authorized User
 
Join Date: Oct 2004
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.

 
Old November 23rd, 2005, 11:25 AM
Authorized User
 
Join Date: Nov 2005
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
 
Old November 23rd, 2005, 11:38 AM
Authorized User
 
Join Date: Oct 2004
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?






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





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