0

I need help for this task in excel, I have one large file and in there it has filters, first I need to filter it for FCDP 21, and that values I have to copy, then to repeat that filtering but for MSR 21, and there to paste that values, here is the code that I wrote but it doesnt work when it needs to paste the values.

Dim s As Range
Dim visible_source_cells As Range
Dim destination_cells As Range
Dim source_cell As Range
Dim dest_cell As Range
With Range("A2:R667")
        .AutoFilter field:=5, Criteria1:="FCDP 21"
  End With
Set s = Application.Selection
s.SpecialCells(xlCellTypeVisible).Select
Set visible_source_cells = Application.Selection
Set Source = Application.InputBox("Please select the destination cells:", Type:=8)
 With Range("A2:R667")
        .AutoFilter field:=5, Criteria1:="MSR M-1 21"
  End With
Set destination_cells = Application.InputBox("Please select the destination cells:", Type:=8)
For Each source_cell In Source
source_cell.Copy
    For Each dest_cell In destination_cells
If dest_cell.EntireRow.RowHeight <> 0 Then
dest_cell.PasteSpecial
Set destination_cells = dest_cell.Offset(1).Resize(destination_cells.Rows.Count)
Exit For
End If
Next dest_cell
Next source_cell
End Sub```
braX
  • 11,506
  • 5
  • 20
  • 33
Luka Strbac
  • 3
  • 2
  • 2
    [*"It's not working"* is not helpful](http://idownvotedbecau.se/itsnotworking/). Note that you cannot paste in filtered ranges because they are non-continous ranges. • To improve your question please read [mcve] and provide a full example with input data and desired output data. Screenshots might help as well. Finally you need to ask a question that we can answer. – Pᴇʜ Oct 11 '21 at 11:32
  • Point one, [avoid `Select` wherever possible](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba), point two, you need to indicate more specifically where the breakdown in your code is, e.g., step-through (F8) to determine that the initial range is actually targeted, that it is copied, etc. You may also benefit from some proper indentation to more easily see what's going on within your code. – Cyril Oct 11 '21 at 12:45
  • So the breakdown in my code is when it needs to paste values to selected range, it pastes random stuff, and go throw infinity loop – Luka Strbac Oct 12 '21 at 06:44

1 Answers1

0

Although it's possible in vba I recommend you use powerQuery for this task.

  1. Select A1 of your source data
  2. Go to menu: Data > Get Data > from table range > select your full table and check the box if you have headers.

Excel will open "PowerQuery"

  1. Add your filters (triangle next to the column names)
  2. Click on "close and load to" choose table, select where you want your filtered table, hit ok.
  3. Save your file.

Every time you update the source table (can also be external file) you just need to hit the data > refresh button and the filtered table will be updated.

ceci
  • 589
  • 4
  • 14
  • I need it to be in vba so if you can help me with code ill be greatfull – Luka Strbac Oct 12 '21 at 06:41
  • not sure I understand why "you need it to be in vba"? PowerQuery is natively available in Excel just like vba. what am I missing? – ceci Oct 12 '21 at 09:41
  • What I need to do: I have a table and in that table I column C I have 4 categories, two main one is FCDP21 and MSR M-1, my task is to copy values from FCDP 21 to MSR M-1, for jan,feb,mar,apr,june etc.. bcs it is al filtered i cant figure it out how to write the script – Luka Strbac Oct 12 '21 at 10:39