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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 9, 2021 at 11:43 AM
Related:
- Copy and autopast rows from sheet1 to sheet2 IF
- How to delete rows and columns in word - Guide
- Excel insert multiple rows - Guide
- Repeat rows in excel - Guide
- Macro to insert row in excel based on criteria ✓ - Excel Forum
- If a1= active, copy a2-a6 to a1-a5 on sheet2 - Excel Forum
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Nov 8, 2021 at 12:03 PM
Nov 8, 2021 at 12:03 PM
Hi Majk,
Have a look at the code below:
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
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
Hi.
Thnks
Dosnt seems to work correct.
Changed the sheet-name, got sub-error,
Walk me throu the setup here?
Ragards
Thnks
Dosnt seems to work correct.
Changed the sheet-name, got sub-error,
Walk me throu the setup here?
Ragards
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
555
Updated on Nov 9, 2021 at 11:44 AM
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
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
