Copy and autopast rows from sheet1 to sheet2 IF

Closed
Majk - Nov 6, 2021 at 11:00 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 9, 2021 at 11:43 AM
Hello,

I woul like to
Copy and autopast rows from sheet1 to sheet2 IF they are f ex "highlighted" or "bold" or "underlined"

is something like this possible?

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Nov 8, 2021 at 12:03 PM
Hi Majk,

Have a look at the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub

If Target.Interior.ColorIndex > 0 Or Target.Font.Bold = True Or Target.Font.Underline = xlUnderlineStyleSingle _
Then Target.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


This code will look at column A and whenever a change is made to the cell contents, the code will check if the cell is highlighted, bold or underlined. If so, then the row is copied to the first available row of Sheet2.

The code will only work when a change is made to the cell contents, NOT the cell format. Change the letter A from code line 2 into the column letter you enter the last of your data. Apply the format before you enter the last of your data. When you apply the format after entering all your data for that row, you can still double click the cell from the chosen column and hit enter to confirm the cells content (which will count as a change and trigger the code).

Let us know if you have further questions.

Best regards,
Trowa
0
Hi.
Thnks

Dosnt seems to work correct.
Changed the sheet-name, got sub-error,
Walk me throu the setup here?

Ragards
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Updated on Nov 9, 2021 at 11:44 AM
Hi Majk,

Have a look at the file below. I put 5 rows of data in Sheet1, with row 1 being the header. 2nd row contains no format, 3rd row is highlighted, 4th row is underlined and the 5th row is bold.

Double click cell A2 and hit enter. Do the same for A3, A4 and A5. Now goto Sheet2 and you will notice that the 3rd, 4th and the 5th row are copied here, but not the 2nd row.

Here is the file:
https://wetransfer.com/downloads/3398cfed2f53476c3bab93a89ecd2c9d20211109164317/ea4fe5

If you can't figure it out with my sample file, then consider uploading your own file (always be careful with sensitive data).

Best regards,
Trowa
0