+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 17

Loop through Column and Copy/paste if cell = text

  1. #1
    Registered User
    Join Date
    11-02-2018
    Location
    London
    MS-Off Ver
    2013
    Posts
    7

    Question Loop through Column and Copy/paste if cell = text

    Hi,

    Im trying to create a loop though column B, that will copy and paste rows D-G into a new worksheet. Im very new to VBA and am just testing some out some code, this is what i have so far:

    Sub looptest()

    Dim i As Long
    Dim a As Long

    Dim da As Worksheet: Set da = Worksheets("Data")
    Dim ap As Worksheet: Set ap = Worksheets("Apples")
    Dim og As Worksheet: Set og = Worksheets("Oranges")

    a = 4
    a = a + 1

    For i = 2 To Rows.Count

    If da.Cells(i, 2) = "Apples" Then

    ap.Range("i,4:i,7").Copy
    og.Range("a,2:a,5").Paste

    End If

    Next i

    End Sub

    I am getting the error Run Time error '1004': Application defined object defined error. Any help on this would be much appreciated.

    thanks

  2. #2
    Registered User
    Join Date
    10-30-2018
    Location
    London
    MS-Off Ver
    excel 2010
    Posts
    53

    Re: Loop through Column and Copy/paste if cell = text

    ap.range(cells(i,4),cells(i,7)).select
    selection.copy
    try that correction first

    a=4
    a=a+1
    the above may give you some problems too.."a" may always go back to 4
    suggest finding the right place to put a=a+1


    ps. mark as solved if it is and consider marking me up if i have been of assistance

    happy learning
    Last edited by Brian Itotia; 11-02-2018 at 08:34 AM.

  3. #3
    Registered User
    Join Date
    11-02-2018
    Location
    London
    MS-Off Ver
    2013
    Posts
    7

    Question Re: Loop through Column and Copy/paste if cell = text

    Hi Brian,

    thanks for the below, unfortunately i am receiving the same error message. I have amended the past line to:

    og.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues

    I have also removed the "a" for the time being to solve one problem at a time

  4. #4
    Registered User
    Join Date
    10-30-2018
    Location
    London
    MS-Off Ver
    excel 2010
    Posts
    53

    Re: Loop through Column and Copy/paste if cell = text

    try
    ActiveSheet.Paste Destination:=og.range(cells(i, 2), Cells(i, 5))

    im not trying it myself but see how that works.
    there are many ways to skin a cat you will find when it comes to vba and coding generally.
    so you will later on also find other ways it can be done.

    i think: ActiveSheet.Paste Destination:=og.range(cells(i, 2))
    also applies for pasting.
    Last edited by Brian Itotia; 11-02-2018 at 09:45 AM.

  5. #5
    Registered User
    Join Date
    10-30-2018
    Location
    London
    MS-Off Ver
    excel 2010
    Posts
    53

    Re: Loop through Column and Copy/paste if cell = text

    please markup if helped and mark as solved if sorted
    thanks

  6. #6
    Registered User
    Join Date
    11-02-2018
    Location
    London
    MS-Off Ver
    2013
    Posts
    7

    Re: Loop through Column and Copy/paste if cell = text

    Sorry, unfortunately this has not worked and i am receiving the same error, i will keep trying!

  7. #7
    Registered User
    Join Date
    10-30-2018
    Location
    London
    MS-Off Ver
    excel 2010
    Posts
    53

    Re: Loop through Column and Copy/paste if cell = text

    are you stepping through your code?

  8. #8
    Registered User
    Join Date
    11-02-2018
    Location
    London
    MS-Off Ver
    2013
    Posts
    7

    Re: Loop through Column and Copy/paste if cell = text

    it is showing the error message after the line:

    ap.Range(Cells(i, 4), Cells(i, 7)).Select

  9. #9
    Registered User
    Join Date
    10-30-2018
    Location
    London
    MS-Off Ver
    excel 2010
    Posts
    53

    Re: Loop through Column and Copy/paste if cell = text

    just reviewed it a second ago.
    im not sure what your trying to do but your code as is says:

    --If da.Cells(i, 2) = "Apples" Then 'if sheet Data.cells 2, column 2 (add .value to refer to what is the value in the cell) eg. da.Cells(i, 2) .value= "Apples" then

    --ap.range(cells(i,4),cells(i,7)).select
    selection.copy --ap is a different sheet(is that your intention) ?

    if so, you are using the same "i" counter on a diferent worksheet. this is throwing you off
    to see it work, change the ap here:

    ap.range(cells(i,4),cells(i,7)).select --to da (data sheet)

    as your learning, ill let you figure it out from there.

    you must be very precise with vba code.
    it can also work with the same "i" counter on Apples sheet(ap) but you havent told excel what sheet to go to yet to copy.

    i hope this helps and keep at it. all been there.
    Last edited by Brian Itotia; 11-02-2018 at 01:22 PM.

  10. #10
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    2,012

    Re: Loop through Column and Copy/paste if cell = text

    Take it a step at a time - make sure you are reading the criteria first...change this line to check
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    11-02-2018
    Location
    London
    MS-Off Ver
    2013
    Posts
    7

    Re: Loop through Column and Copy/paste if cell = text

    Very simple mistake! was meant to be:

    If da.Cells(i, 2).Value = "Apples" Then

    da.Range(Cells(i, 4), Cells(i, 7)).Select
    Selection.Copy
    ActiveSheet.Paste Destination:=ap.Range(Cells(i, 2), Cells(i, 5))

    this now gives an error message after the paste line. i will keep trying!

  12. #12
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    excel 2016 64bit - Windows 10
    Posts
    2,012

    Re: Loop through Column and Copy/paste if cell = text

    Not sure if you can reference a range with
    Please Login or Register  to view this content.
    I couldn't get it to work

  13. #13
    Registered User
    Join Date
    10-30-2018
    Location
    London
    MS-Off Ver
    excel 2010
    Posts
    53

    Re: Loop through Column and Copy/paste if cell = text

    You have just learnt why they say give your variables descriptive names
    as well as your sub declarations infact anytime you are naming anything on a computer, this is advised.
    so that when you come back after a month (if you're me, a night on the ****), it all looks like gibberish if its not well described and commented.
    btw. the problem is not in the paste line of the code.
    excel is blind...you must tell it which sheet you SELECT.

  14. #14
    Registered User
    Join Date
    11-02-2018
    Location
    London
    MS-Off Ver
    2013
    Posts
    7

    Re: Loop through Column and Copy/paste if cell = text

    Thanks, the message box is working, i will find a way to figure the others. do you have any suggestions for tutorials?

  15. #15
    Registered User
    Join Date
    10-30-2018
    Location
    London
    MS-Off Ver
    excel 2010
    Posts
    53

    Re: Loop through Column and Copy/paste if cell = text

    its friday
    enjoy! couldnt help it
    Please Login or Register  to view this content.
    note changes to cell and sheet references.
    have good weekend.

+ Reply to Thread
Page 1 of 2 1 2 LastLast

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1