+ Reply to Thread
Results 1 to 8 of 8

copy ... some corrections

Hybrid View

  1. 05-15-2012, 05:13 AM #1
    yiannis1925
    yiannis1925 is offline
    Registered User
    Join Date
    11-14-2011
    Location
    Dusseldorf, Germany
    MS-Off Ver
    Excel 2003
    Posts
    97

    copy ... some corrections

    Hello,

    I would like for your unprofessional help please

    I have the bellow code... and trying to filter from sheet(3) for any rows that contain a value >30 in column F and then copy the column A and N to sheet(2)...

    The problem is that I want to paste them in sheet(2) in A and B columns... and need to copy-paste all the result for the filtered >30 from sheet(3)

    Sub Foldering()

    Sheets(3).Select
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Sheets(3).Range("$A$2:$N$" & LR).AutoFilter Field:=6, Criteria1:=">30", Operator:=xlAnd
    Range("A2:A37144,N2:N37144").Select
    Range("N2").Activate
    Selection.Copy
    Sheets(2).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub
    thank you in advance...
    Reply With QuoteRegister To Reply
  2. 05-15-2012, 05:44 AM #2
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: copy ... some corrections

    Try this code 
    Sub copy()
Dim LR As Long

With Worksheets("Sheet3")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("$A$2:$N$" & LR).AutoFilter field:=6, Criteria1:=">30"
    .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).copy Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End With

End Sub
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  3. 05-15-2012, 05:57 AM #3
    yiannis1925
    yiannis1925 is offline
    Registered User
    Join Date
    11-14-2011
    Location
    Dusseldorf, Germany
    MS-Off Ver
    Excel 2003
    Posts
    97

    Re: copy ... some corrections

    Thank you for the quick reply arlu!!

    your code works good... but the result is not what I need... you copy the row... and I need only A and N columns from Sheet3 to be pasted to A and B in Sheet2...

    here is what I have

    Sub ssss()

    Sheets(3).Select
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Sheets(3).Range("$A$2:$N$" & LR).AutoFilter field:=6, Criteria1:=">30", Operator:=xlAnd
    Range("A2:A37144,N2:N37144").copy
    Sheets(2).Select
    Range("A3").Select
    ActiveSheet.Paste
End Sub
    this seems to work good... but how can I change 

    Range("A2:A37144,N2:N37144").copy
    not to be restricted to row number "37144" ...

    or any other better suggestion
    Reply With QuoteRegister To Reply
  4. 05-15-2012, 08:36 AM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: copy ... some corrections

    Try this 
    Sub copy()
Dim LR As Long
Dim rngdata As Range

With Worksheets("Sheet3")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    Set rngdata = .Range("$A$2:$N$" & LR)
    rngdata.AutoFilter field:=6, Criteria1:=">30"
    If .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Count > 1 Then
        Intersect(rngdata.Offset(1).Resize(rngdata.Rows.Count - 1), .Range("A:A,F:F")).copy _
            Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Else
        MsgBox "No visible data"
    End If
    rngdata.AutoFilter field:=6
End With

End Sub
    Reply With QuoteRegister To Reply
  5. 05-15-2012, 09:24 AM #5
    Pichingualas
    Pichingualas is offline
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: copy ... some corrections

    arlu you are awesome
    .?*??)
    `?.???.?*??)?.?*?)
    (?.?? (?.?    Pichingualas <---
    ??????????????????????????

    Wrap your code with CODE TAGS.
    Thank those who helped you, Don't forget to add to their REPUTATION!!! (click on the star below their post).
    Please mark your threads as [SOLVED] when they are (Thread Tools->Mark thread as Solved).
    Reply With QuoteRegister To Reply
  6. 05-15-2012, 11:07 AM #6
    yiannis1925
    yiannis1925 is offline
    Registered User
    Join Date
    11-14-2011
    Location
    Dusseldorf, Germany
    MS-Off Ver
    Excel 2003
    Posts
    97

    Re: copy ... some corrections

    yep! that is great

    I made a small change, because I want to copy the O column also...

    so the code looks like

    Sub copyXXX()
Dim LR As Long
Dim rngdata As Range

With Sheets(3)
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    Set rngdata = .Range("$A$2:$O$" & LR)
    rngdata.AutoFilter field:=6, Criteria1:=">30"
    If .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Count > 1 Then
        Intersect(rngdata.Offset(1).Resize(rngdata.Rows.Count - 1), .Range("A:A,N:N,O:O")).copy _
            Sheets(2).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Else
        MsgBox "No visible data"
    End If
    rngdata.AutoFilter field:=6
End With

End Sub
    That was the first step of my code!
    now I want to apply this formula =IF(F2<30;0;IF(F2<50;0.2;IF(F2<100;0.4;IF(F2<150;0.6;IF(F2<200;0.8;1))))) for all rows in sheets(3) place it to column "O" and then copy the corresponding values to the Sheets(2) row C...

    Actually I could first run the formula and then use the above code to copy everything...

    Waiting for your help
    Thank you
    Reply With QuoteRegister To Reply
  7. 05-15-2012, 11:23 AM #7
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: copy ... some corrections

    You will have to apply the formula first and then copy the rows.

    But there is a problem with the formula - you are checking if a value is less than 30 and less than 50 and less than 100, etc. What about number 20. It is less than all 3, so which value will it choose? You can try explaining what you are trying to arrive at and we can help you with the formula.
    Reply With QuoteRegister To Reply
  8. 05-15-2012, 12:21 PM #8
    Pichingualas
    Pichingualas is offline
    Forum Contributor
    Join Date
    02-07-2012
    Location
    MIA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    429

    Re: copy ... some corrections

    The formula is ok arlu, it only checks the next step if the previous is false.

    If(condition;what to do if true;what to do if false)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1