Code for selecting data and pasting in another sheet

[Veeru106]

Hi,

I am looking for a code which select data from Data sheet as per criteria mention in Selection sheet and paste in Final sheet.

Thanks in advance

 

[kingcashandcarry]

i use this code for me
just write where to paste after copy
[Sub copyektels()
Dim x As Range
Dim y As Range
Set x = Application.InputBox("Select what copy using the mouse", Type:=8)
x.Copy
End Sub]

 

[Veeru106]

Thank you. but what should be written in "Select what copy using the mouse".

Should i give reference to cell A2 in Selection tab.

Thanks

 

[Veeru106]

I guess code is incomplete , as i need copy data to be pasted in Final tab...and here i dont see any reference given to pasting in any tab...

 

[kingcashandcarry]

sorry for my misunderstood
my code can't help you i think

 

[Veeru106]

Yes it will not...but thanks for looking at it.

Untill now i have come up with this..but it is giving me error in below
Set rng = Sheets("Selection tab").Range("Selection Name here")

Sub Macro3()
Dim rng
Sheets("Data").Select
    Range("A1").Select
    Selection.AutoFilter
    Set rng = Sheets("Selection tab").Range("Selection Name here")
    rng.Cells (1) <> "*" And rng.Cells(1) <> ""
    Selection.Copy
    Sheets("Data").Select
    ActiveSheet.Range("$A$1:$O$29").AutoFilter Field:=1, Criteria1:=Range("A2")
    Range("A2:O5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("FInal Sheet").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C9").Select
    Sheets("Data").Select
    Application.CutCopyMode = False
    Range("B1").Select
    Selection.AutoFilter
    Range("E21").Select
    Sheets("FInal Sheet").Select
End Sub

line

Set rng = Sheets("Selection tab").Range("Selection Name here")

 

[Belleke]

Something like this?

 

[Veeru106]

Great…working great…but can you please explain what following things means, as I need to apply this code on different data, which is has 50000 rows data.

1. ReDim ar1(UBound(ar), 35)
2. For j = 2 To UBound(ar)
3. If ar(j, 1)
4. FS.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(UBound(ar1) + 1, UBound(ar1, 2) + 1) = ar1

Also have some additional requirements


We only need data for Fin and Mkt Dept only


We need same thing happen for HYT in Select name here 2 from A10 cell in Final sheet. Add all numbers of FGT in HYT (That can be done easily with sum function)


At last,data for FGT should be zero after pasting data in Final Sheet and new data should be pasted in HYT from cell D24 onwards.



Thanks for looking at it and giving your time.