Excel VBA copying specific rows and pasting them to specific sheet. Without double inserting the data

rekasi

New Member
Joined
May 2, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello, I am using vba in windows. I have a excel project. I need to have a list of projects, the status of the project color codes the row. Data is sorted by macro that sorts data by its color. I want to have page where all the projects are listed and then one sheet for each status, which are "done" "work in progress" and "discontinued". The problem i am facing is that when I run my the code it deletes it from main registry and if I take the delete row out of the code it will double insert the data. So therefore I am thinking pasting the full table and deleting rows that are not meant to be in ie. "done sheet". Also if I change status in main sheet it should affect sheets with one status only, or at least rearrange sheets to correct places. Can someone help me with the code? I have no idea how to do it, I tried to search other threads, but could not find one with correct code. Thank you in advance. Picture is to help understand what I mean. The text is in finnish dont mind it.

1651493979786.png
 
VBA Code:
Sub lisaa_hanke()
    Dim myrow As ListRow
    Dim introws As Integer
    
    introws = ActiveWorkbook.Worksheets("hankerekisteri").ListObjects("tblhankkeet").ListRows.Count
    Set myrow = ActiveWorkbook.Worksheets("hankerekisteri").ListObjects("tblhankkeet").ListRows.Add
    
    myrow.Range(1) = Range("a3")
    myrow.Range(2) = Range("b3")
    myrow.Range(3) = Range("c3")
    myrow.Range(4) = Range("d3")
    myrow.Range(5) = Range("e3")
    myrow.Range(6) = Range("f3")
    myrow.Range(7) = Range("g3")
    myrow.Range(8) = Range("h3")
    myrow.Range(9) = Range("i3")
    Call jarjesta_hankkeet
    
    
End Sub
VBA Code:
Sub jarjesta_hankkeet()

    With Sheets("hankerekisteri")
        ActiveWorkbook.SlicerCaches("Osittaja_STATUS").ClearManualFilter
        ActiveWorkbook.SlicerCaches("Osittaja_OSASTO").ClearManualFilter
    End With
    ActiveWorkbook.Worksheets("Hankerekisteri").ListObjects("tblhankkeet").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Hankerekisteri").ListObjects("tblhankkeet").Sort. _
        SortFields.Add(Range("tblhankkeet[NIMI]"), xlSortOnCellColor, xlAscending, , _
        xlSortNormal).SortOnValue.Color = RGB(255, 192, 0)
    ActiveWorkbook.Worksheets("Hankerekisteri").ListObjects("tblhankkeet").Sort. _
        SortFields.Add(Range("tblhankkeet[NIMI]"), xlSortOnCellColor, xlAscending, , _
        xlSortNormal).SortOnValue.Color = RGB(169, 208, 142)
    ActiveWorkbook.Worksheets("Hankerekisteri").ListObjects("tblhankkeet").Sort. _
        SortFields.Add(Range("tblhankkeet[NIMI]"), xlSortOnCellColor, xlAscending, , _
        xlSortNormal).SortOnValue.Color = RGB(255, 51, 0)
    With ActiveWorkbook.Worksheets("Hankerekisteri").ListObjects("tblhankkeet"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

I have another problem. When pressing using the first macro code it does not deselect the table, which could lead to human errors. Is there a quick fix for this?
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
The code selects all cells in tblhankkeet, which is weird due when I run jarjesta_hankkeet alone it does not leave the tbl selected. But run lisaa_hanke, and I move to look "hankerekisteri" tblhankkeet is selected and I would like not to be selected due someone can press key and delete all the content etc.
 
Upvote 0
If the "hankerekisteri" sheet is the active sheet when the code is run, insert this line of code at the end of the jarjesta_hankkeet macro:
VBA Code:
Sheets("hankerekisteri").Range("A1").Select
 
Upvote 0

Forum statistics

Threads
1,223,992
Messages
6,175,822
Members
452,672
Latest member
missbanana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top