• 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...

  • 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

A

Abhijeet

Active Member
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
 

Attachments

  • Element Wise.xlsx
    13 KB · Views: 6
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.
​
 
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
 

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 …
​
 
Code: 
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
 
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 …
​
 
In criteria range mention does not select blank Cell <> this type of criteria mention this macro work for 1st sheet
 

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

So your result from "enh" worksheet is good or not ?​
 
Hi

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

Attachments

  • Element Wise.xlsm
    21.1 KB · Views: 7
Yes of course it can not work !
And it is not the code but just your criteria as I yet wrote :
Marc L said:
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" …
Click to expand...
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 …
​
 
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
 
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 :
Marc L said:
better use an advanced filter to just filter worksheet
and use as yet written Range.Copy method
for each consecutive columns block.
Click to expand...
Combine an advanced filter and Range.Copy method by worksheet
or better within a loop upon source worksheets …
​
 
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
 
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 …
​
 
Please tell me in this code what need to change i tried last row method but not work
Code: 
Sheets("Enh").Columns("A:V").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range("N1:T8"), CopyToRange:=Range("A & LastRow:H & LastRow"), Unique:=False
 

As LastRow is not a method but just a variable,
how do you calculate it before this codeline ?
​
 
Code: 
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:

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

N1:T8 -> so you forgot columns Value8 & 9 …
​
 

Ok if you will never have some data in those columns
but not if using same code for several worksheets …​
 
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
 
Abhijeet said:
Is their any possibility to multiple worksheets pull data in one sheet with help of Advanced filter
Click to expand...
Yes as I yet wrote since post #2 !

Abhijeet said:
I think its complex because how to give criteria for checking next empty row for paste the data
Click to expand...
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 …
​
 
You must log in or register to reply here.
Back
Top