1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sheet Wise Particular Range Copy paste data

Discussion in 'VBA Macros' started by Abhijeet, Apr 2, 2017.

  1. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Hi

    I have 3 Sheets in each sheet Different range in Sheet Basic Range is N2:Last row of data

    In Sheet Enh Range is N2:T last row of data

    In Sheet OT Range is N2:V last row of data

    all 3 sheets data pull in Expeceted Result sheet particular format above sheet wise range is any value then copy paste that range data in Expeceted Result from column M & Column A need to pull Number of that row & AA column pull File Name & In Column C Effective Date that is which is current month that 1st day that date i want in this column

    In Column D Element Name This is Sheet Wise for Basic "BASIC NR" , for Enh sheet "ENHANCEMENT NR NP" , for OT sheet "OVERTIME NR NP" this name need to input like this

    In attach file show how data look like in Expeceted Result sheet

    Please tell me how to do this

    Attached Files:

  2. Marc L

    Marc L Excel Ninja

    Messages:
    3,045

    Hi !

    Do not use autofilter but advanced filter …
    Chirag R Raval likes this.
  3. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Hi

    This method is tricky for me sheet wise how to select different range & paste into new sheet
  4. Marc L

    Marc L Excel Ninja

    Messages:
    3,045
    It is easier & safer …
    Or you can test each row of each worksheet, but slowest way.

    As a good code does not select anything, it just directly works with objects …
    Just see in VBA inner help Range.Copy method.
    Chirag R Raval likes this.
  5. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    I tried with Advanced filter but not work for Sheet2 & Sheet 3 i do not have any idea why not work can any one please tell me how to do this
  6. Marc L

    Marc L Excel Ninja

    Messages:
    3,045

    If code works for Sheet1 so it works for Sheet2 & 3 !

    I do not have neither an idea why your code does not work
    as I'm not a mind reader ! Better is you post your code here …
  7. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Code (vb):
    Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
       Range(Selection, Selection.End(xlToRight)).Select
        Range("A1:J2").Select
        Sheets("Enh").Columns("A:V").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("N1:V2"), CopyToRange:=Range("A1:B1"), Unique:=False
    End Sub
    Chirag R Raval likes this.
  8. Marc L

    Marc L Excel Ninja

    Messages:
    3,045
    What are exactly criteria range in N1:V2 ?

    So you just copy result for A & B destination columns.
    For non consecutive result columns, better use an advanced filter
    to just filter worksheet and use as yet written Range.Copy method
    for each consecutive columns block.

    Two ways for criteria :

    • via a range. As here your conditions for columns are "OR" type,
    the range criteria needs a row header plus a row by criteria column
    so a total with headers of 10 rows !
    As columns criterion on same row means "AND" and not "OR" …

    • Via a formula. (Often easy and here at beginner level !)
    A formula range criteria needs a column of two cells (in rows #1 & 2) :
    - first one (optional, can be blank) for a header
    different of the source headers,
    - second cell contains the formula which result must be TRUE
    for rows to copy to result worksheet …
    If you do not like easy and instant advanced filter,
    you can browse each row of each worksheet via a classic loop …
    Chirag R Raval likes this.
  9. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    In criteria range mention does not select blank Cell <> this type of criteria mention this macro work for 1st sheet
  10. Marc L

    Marc L Excel Ninja

    Messages:
    3,045

    Can you show exactly what is your criteria range ? Or join workbook …

    So your result from "enh" worksheet is good or not ?​
  11. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Hi

    Please check macro for Basic sheet its work but not work for Enh

    Attached Files:

  12. Marc L

    Marc L Excel Ninja

    Messages:
    3,045
    Yes of course it can not work !
    And it is not the code but just your criteria as I yet wrote :
    Actualy you ask for an "AND" but you need an "OR" operator
    with a line by column criteria as N1:V10 as range criteria,
    each column criteria must be alone in its row among range criteria …
    Chirag R Raval likes this.
  13. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Then please tell me what is the criteria for Enh & OT sheet

    Data is huge this is dummy data
  14. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Sorry i got it what you are told me its work now problem how this macro pull data from each sheet in Expected result sheet
  15. Marc L

    Marc L Excel Ninja

    Messages:
    3,045
    Instead of that kind of range criteria
    you can also use an easy formula criteria …

    Now first, advanced filter code must not copy as I yet wrote :
    Combine an advanced filter and Range.Copy method by worksheet
    or better within a loop upon source worksheets …
  16. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Yes that i know but please tell me when macro run Basic sheet Data pull in Expected Result sheet then macro move to Enh Sheet then how to identify last empty row

    how to change in coding part please tell me
    Chirag R Raval likes this.
  17. Marc L

    Marc L Excel Ninja

    Messages:
    3,045
    Last empty row is always the last worksheet line aka # 1 048 576 !

    For last used row, as yet showed in dozains of your previous thread
    you have the choice via
    • UsedRange
    • CurrentRegion
    • End property …
  18. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Please tell me in this code what need to change i tried last row method but not work
    Code (vb):
    Sheets("Enh").Columns("A:V").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("N1:T8"), CopyToRange:=Range("A & LastRow:H & LastRow"), Unique:=False
     
    Chirag R Raval likes this.
  19. Marc L

    Marc L Excel Ninja

    Messages:
    3,045

    As LastRow is not a method but just a variable,
    how do you calculate it before this codeline ?
  20. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Code (vb):
    Sub Enh()
    Dim LastRow As Long
    With ActiveSheet
            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        End With
      Range(Selection, Selection.End(xlToRight)).Select
        'Range("A1:J2").Select
       Sheets("Enh").Columns("A:V").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=Range("N1:T8"), CopyToRange:=Range("A & LastRow:H & LastRow"), Unique:=False
    End Sub
    Last edited: Apr 3, 2017
    Chirag R Raval likes this.
  21. Marc L

    Marc L Excel Ninja

    Messages:
    3,045

    CriteriaRange:=Range("N1:T8"), CopyToRange:=Range("A" & LastRow & ":H" & LastRow)

    N1:T8 -> so you forgot columns Value8 & 9 …
  22. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    No For Enh Sheet Till Value7 i.e T8

    For OT Sheet Value9 i.e V10
  23. Marc L

    Marc L Excel Ninja

    Messages:
    3,045

    Ok if you will never have some data in those columns
    but not if using same code for several worksheets …​
  24. Abhijeet

    Abhijeet Active Member

    Messages:
    1,190
    Is their any possibility to multiple worksheets pull data in one sheet with help of Advanced filter

    I think its complex because how to give criteria for checking next empty row for paste the data
  25. Marc L

    Marc L Excel Ninja

    Messages:
    3,045
    Yes as I yet wrote since post #2 !

    First empty row is not an advanced filter criteria
    but just the Range.Copy method Destination argument
    - as you must see in VBA inner help as in your previous threads ! -
    after a just filter advanced filter - and not a direct copy ! - as
    I several times wrote in previous posts, read posts #8 & 15 !
    And you already have a way as you posted yourself using End property …
    Chirag R Raval likes this.

Share This Page