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

copy and paste filtered data to another sheet

Vipin Aeri

New Member
Hallo all, I am stuck at one point. as I am unable to write a macro to copy my filtered data to another sheet in the same workbook.
I tried the following Macro but it works only when I have other filtered data. you can see it in my macro too. But when I re filtered the same data for another creiteria.
Then it doesnot work. Please help me and let me know where I am making a mistake. my code is :

>>> use code - tags <<<
Code:
Sub MoveData()

Dim a, Lr&

With Sheets("WORKSHEET")
Lr = .Range("A" & Rows.Count).End(3).Row
a = .Range("A6:G" & Lr)

End With

With Sheets("STOCK TRACKING TEMPLATE")
Lr = .Range("E" & Rows.Count).End(3).Row
.[E1:I1].Offset(Lr).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 2, 3, 6, 5))
End With

End Sub
 

Attachments

Last edited by a moderator:

Vipin Aeri

New Member
For one thing, your rows.count need the sheet prefix. Since you used With, use: .Rows.Count
I have changed it so but it gives now the Error. I will be thankful if you can help me and explain me where I am making the mistake


>>> as many times You ( Vipin Aeri ) have noticed <<<
>>> use code - tags <<<

Code:
Sub MoveData()

Dim a, LR&

With Sheets("WORKSHEET")
  LR = .Range("A" & Rows.Count).End(.xlUp).Row
  a = .Range("A6:G" & LR).SpecialCells(xlCellTypeVisible).Select

End With

With Sheets("STOCK TRACKING TEMPLATE")
  LR = .Range("E" & Rows.Count).End(3).Row
  .[E1:I1].Offset(LR).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 2, 3, 6, 5))
End With

End Sub
 

Attachments

Last edited by a moderator:

Kenneth Hobson

Active Member
Untested but I did add the periods for the rows.count as I explained. I also removed the period from .xlUp. I am not sure why compile did not find that error.
Code:
Sub MoveData()

Dim a, LR&

With Sheets("WORKSHEET")
  LR = .Range("A" & .Rows.Count).End(xlUp).Row
  a = .Range("A6:G" & LR).SpecialCells(xlCellTypeVisible)

End With

With Sheets("STOCK TRACKING TEMPLATE")
  LR = .Range("E" & .Rows.Count).End(3).Row
  .[E1:I1].Offset(LR).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 2, 3, 6, 5))
End With

End Sub
 
Last edited:

Kenneth Hobson

Active Member
I don't know that the 3 was meant to do. I guess this sort of does what you want? Be sure to test in backup copy for things like this.
Code:
Dim a, LR As Long
  With Worksheets("WORKSHEET")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    a = .Range("A6:G" & LR).SpecialCells(xlCellTypeVisible)
  End With

  With Worksheets("STOCK TRACKING TEMPLATE")
    LR = .Range("E" & .Rows.Count).End(xlUp).Row + 1
    .[E1:I1].Offset(LR).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 2, 3, 6, 5))
  End With
End Sub
 

Vipin Aeri

New Member
I don't know that the 3 was meant to do. I guess this sort of does what you want? Be sure to test in backup copy for things like this.
Code:
Dim a, LR As Long
  With Worksheets("WORKSHEET")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    a = .Range("A6:G" & LR).SpecialCells(xlCellTypeVisible)
  End With

  With Worksheets("STOCK TRACKING TEMPLATE")
    LR = .Range("E" & .Rows.Count).End(xlUp).Row + 1
    .[E1:I1].Offset(LR).Resize(UBound(a)) = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 2, 3, 6, 5))
  End With
End Sub
It works but it copies only two rows of my filtered data. I will thankful if you check my sample file and see it. Thanks in advance
 

Attachments

Kenneth Hobson

Active Member
I am not sure why Advanced Filter is not used. I am not sure why xlCellTypeVisible was used. Maybe you filtered manually?

If you can block color yellow your data to copy and then block color a manual pasted block red in another worksheet, I can see what you expected. Then attach that marked up file.

Be aware though that with your formatting, the End Up does not really go up to cell with real data. It goes up to the formatted data cells. You might want to look further down in the pasted block to see if it pastes there.
 

Vipin Aeri

New Member
I am not sure why Advanced Filter is not used. I am not sure why xlCellTypeVisible was used. Maybe you filtered manually?

If you can block color yellow your data to copy and then block color a manual pasted block red in another worksheet, I can see what you expected. Then attach that marked up file.

Be aware though that with your formatting, the End Up does not really go up to cell with real data. It goes up to the formatted data cells. You might want to look further down in the pasted block to see if it pastes there.
I am attaching the file as required by you. let me explain you my whole copying steps . As I am not aware of advanced filter so I used normal codeing.
First of all I filtered the worksheet with vba code (Module I) and then with second code I try to copy it to the main template. At the time of copying my filtered to data I have to change the columns as per my template postions. I am enclosing the Main worksheet too from where I filter the data for copying thru my Module 1 code
 

Attachments

Top