adding new row and copying last filled row worked but after highlighting active cell event stopped working

  • hello :)

    I love learning vba there are so many ways to be creative...

    but one of the most annoying things is this: you succeeded in creating a good thing, so you start trying the next creation...

    and when that's done...? the creation before is not working anymore :( ...

    what did i do?

    I created vba to copy the last filled in row and add it as new last row, deleting all data except formula. after that

    i could go through all cells in that row and fill in all new data.

    it worked perfectly !!!

    next goal: highlight the active cell with borders or background color... they worked too slow, so i just make text of the activecell bold

    and when clicking the next cell, the bold disappears...

    it worked perfectly too !!!

    but i notice now that my adding new row vba is stopping...

    to make the activecell highlight in bold, i used :

    is the worksheet activate event the reason of my adding new row vba not working,

    in that vba this is the code :

    Range("A" & nieuwerij - 1 & ":CQ" & nieuwerij - 1).Select
        Range("A" & nieuwerij).Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
        Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
            , SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Range("A" & nieuwerij & ":CQ" & nieuwerij).SpecialCells(xlCellTypeConstants).ClearContents

    the error appears on line 4 : "selection.pastespecial paste ...

    what has changed that i don't understand ?

  • VBA can be extremely useful, but selecting cells in VBA is generally unnecessary.

    Also, it is more efficient to to use inbuilt Excel features. From your explanation I would say Excel's Table feature would be a much better option.

    Overview of Excel Tables.

    I am sure that I have pointed this out to you before.

  • hello ;)

    i did try already twice to convert to a table but nothing works when i do that... the first thing i try are macros...

    all macros i try give errors, and being a basic user i have no idea where and what i should have to change in vba...

    of course when you do look up convert to table i see all good things, but nowhere i read about things you lose...

    so thank you but for now no table...

  • You don't lose anything. What are you trying?


    the first thing i try are macros...

    all macros i try give errors, and being a basic user i have no idea where and what i should have to change in vba...

    Do you mean you are trying code to convert to Tables?

    Converting to Tables won't work if you have completely empty rows or columns in the data, but if you have then Excel's Database features won't work either.

  • that's exact as i thought: tables wont help me...

    at this moment i'm exploring solutions for two problems:

    1. a link between the name of mom and dad in a person's row to the row of mom or dad;

    and 2. highlight the active cell (text in bold or thick borders for ex.)

    thx for the explanation :)

  • hello :)

    can someone explain to me the effect from that event to highlight the active cell making my macro to add and fill new row

    doesnt work anymore? ...

  • hello ;) these are the pieces that work separately :

    1. vba to search column for a name:

    and secondly, event vba to put text in bold and add yellow thick border around activecell, only when active ( in event selection change):

    they both work when the other is deactivated (i put ' before the line to make them a remark) ...

    i now only use the event activecell with bold text that works perfectly together with the rest... :

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static PrevCell As Range
    If Not PrevCell Is Nothing Then
    PrevCell.Font.Bold = False
    End If
    Target.Font.Bold = True
    Set PrevCell = Target
  • It's very difficult to help you because you never answer questions and seem to ignore what you are told.


    Without seeing an example of your worksheet it's hard to help. You say Tables won't help, but why?

  • hello ;)

    I certainly do no ignore people... I am working (having fun) in excel every day and I have more then one item i'm trying to construct.

    sorry that i'm not going for tables but they do seem to changes too much, dont forget i'm just a basic, all that i achieve is not by knowing but by finding and trying...

    what questions do you have ?

    attached the royalty version of my file, feel free to try :) ! and thank you for your time !adressen 21 royal.xlsb

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!