Sheet Wise Particular Range Copy paste data

[Abhijeet]

Can you please tell me this how to do in my case

 

[Marc L]

So I try to explain again :
currently you use a copy action advanced filter
but I wrote to not use this action
- 'cause you have several source worksheets ! -
as AdvancedFilter method has only two actions types,
so it's very easy to change, just open VBA inner help !

When a worksheet is filtered via this method,
use Range.Copy method from source filtered worksheet
to result worksheet (again to see in VBA inner help).

Once done, redo same operations on other source worksheets …

If you are not in comfort with an advanced filter,
just browse each row of every source worksheet …
​

 

[Abhijeet]

Sir i tried but not do this can u please help me

 

[Marc L]

I wanna see your try …​

 

[Abhijeet]

Sub Enh()
Dim lastrow As Long

lastrow = Sheets("Sheet1").Range("A65536").End(xlUp).Row + 1
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), Unique:=False
End Sub

 

[Marc L]

Don't forget to use code tags or appropriate code icon !

What is Action type in your advanced filter ?
And what I many times wrote to not use as action ?!

I told you to not copy but to just filter with AdvancedFilter method …

If you do not copy this beginner level option, do not ever use this method.
​

 

[Abhijeet]

sorry i do not know apart from this if you want to tell me then please or leave it i will try other method

 

[Marc L]

Within your code place blinking text cursor on AdvanceFilter

then hit

key then read from VBA inner help what is the only one
other Action argument as xlFilterCopy is not here the valid option !

If you really do not want to follow what a 13 years old child succeeded
two hours ago with your attachment, forget this easy & fast method !
(He even found the criteria very easy formula way logic !)

Or as a classic messy gas factory method,
browse and test each used row of every source worksheet …
​

 

[Abhijeet]

I have also know easy formula Count

 

[Abhijeet]

Declaration
Public Enumeration XlFilterAction
'Usage
Dim instance As XlFilterAction

This i found
xlFilterCopy Copy filtered data to new location.
xlFilterInPlace Leave data in place.

 

[Marc L]

Yes, for an easy formula criteria COUNT is the best choice ‼

Ok, just use AdvancedFilter in place
(xlFilterInPlace instead of xlFilterCopy)
and for a result layout as your original attachment
use Range.Copy Destination:= to copy each consecutive
columns blocks to result worksheet.
Once done for a source worksheet, repeat same operations for others …
​

 

[Abhijeet]

I tried to paste this but below particular column not paste can u pls tell me what need to change in code

Sheets("Basic").Application.Union(Range("B1:B" & LR), Range("N1:N" & LR), Range("AA1:AA" & LR)).SpecialCells(xlCellTypeVisible).Copy _
Sheets("All Core Data").Range("A1")
Sheets("ENHANCEMENTS").Application.Union(Range("B1:B" & LR), Range("N1:T" & LR), Range("AA1:AA" & LR)).SpecialCells(xlCellTypeVisible).Copy _
Sheets("All Core Data").Range("a65356").End(xlUp).Offset(1, 0)

 

[Marc L]

Worksheet reference is always just before Range or within !​

Union(Range("Basic!B1:B" & LR), Range("Basic!N1:N" & LR), Range("Basic!AA1:AA" & LR)).Copy Range("'All Core Data'!A1")

And SpecialCells is useless here …
​

 

[Abhijeet]

Hi

I tried its work now just problem how to paste this data in particular columns

Pls check All Core Data Sheet

 

[Marc L]

First on result worksheet define all needed columns.

By code on each source worksheet :
• apply an advanced filter.

• Once worksheet is filtered, just use Range.Copy Destination:= method
for each block of consecutive columns (refering to initial attachment).
If destination worksheet has not the same layout of initial attachment
aka now all columns in a single block, you can use Union method
as source of Copy method.
To avoid to copy source headers row, use Rows or Offset property …
​

 

[Abhijeet]

Can you please tell me in this code what need to change to paste different column with range type

Union(Range("Basic!B1:B" & LR), Range("Basic!N1:N" & LR), Range("Basic!AA1:AA" & LR)).Copy Range("'All Core Data'!A1")

 

[Marc L]

I do not copy what you mean by « range type » ?!

As this codeline seems correct if LR is superior to 1
but do not copy row #1 start from row #2 !

Union(Range("Basic!B2:B" & LR), Range("Basic!N2:N" & LR), Range("Basic!AA2:AA" & LR)).Copy Range("'All Core Data'!A2")

For destination better use End property
if you copy several worksheets into one …

As LR variable is useless for source data
just respecting Excel object model using a With block :​

    With Worksheets({SourceWorksheet}).UsedRange.Rows
' some stuff
          .AdvancedFilter xlFilterInPlace, {CriteriaRange}
' some stuff
           With .Item("2:" & .Count).Columns
              Union(.Item(2), .Item(14),.Item(27)).Copy {DestinationRange}
          End With
' some stuff
    End With

Replace {text} with correct reference …
​