- Duplicate rows from multiple worksheets
- Copy and insert rows n number of times (Solved) » Forum - Excel
- How to split data into multiple worksheets? » How-To - Excel
- Excel - Conditional hide rows in multiple sheets » How-To - Excel
- Excel - How to highlight duplicate rows? » How-To - Excel
- Excel - A macro to copy rows X number of times » How-To - Excel
8 replies
Report
Respond to iksta
Yes.
Hello Iksta,
The following two codes should do the task for you.
Firstly:-
This code will filter Column G in all sheets (except the Follow Up sheet) for the criteria "Follow Up". It then transfers the relevant rows of data to the Follow Up sheet. Place the code in a standard module and assign it to a button.
Secondly:-
When you type the word Complete in any cell in Column G of the Follow Up sheet then click away (or press enter or down arrow), the relevant row of data will be deleted from the Follow Up sheet. This code does not need a button and needs to be placed in the Follow Up sheet module. So, to implement this second code:-
- Right click on the Follow Up sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the second code.
Following is a sample workbook that I have prepared for you so that you can see how the codes work:-
http://ge.tt/4rAUiip2
Click on the "RUN" button to see the data from the source sheets transferred to the Follow Up sheet.
When you have some data in the Follow Up sheet, overwrite "Follow Up" in any cell in Column G with "Complete" then click away (or press enter or down arrow). You'll note that the relevant row of data is deleted.
I hope that this helps.
Cheerio,
vcoolio.
The following two codes should do the task for you.
Firstly:-
Sub FollowUp()
Dim ws As Worksheet
Application.ScreenUpdating = False
Sheet1.UsedRange.Offset(1).ClearContents
For Each ws In Worksheets
If ws.Name <> "Follow Up" Then
With ws.[A1].CurrentRegion
.AutoFilter 7, "Follow Up", , , 7
.Offset(1).EntireRow.Copy
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
.AutoFilter
End With
End If
Next ws
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
This code will filter Column G in all sheets (except the Follow Up sheet) for the criteria "Follow Up". It then transfers the relevant rows of data to the Follow Up sheet. Place the code in a standard module and assign it to a button.
Secondly:-
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "Complete" Then
Target.EntireRow.Delete
End If
End Sub
When you type the word Complete in any cell in Column G of the Follow Up sheet then click away (or press enter or down arrow), the relevant row of data will be deleted from the Follow Up sheet. This code does not need a button and needs to be placed in the Follow Up sheet module. So, to implement this second code:-
- Right click on the Follow Up sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the second code.
Following is a sample workbook that I have prepared for you so that you can see how the codes work:-
http://ge.tt/4rAUiip2
Click on the "RUN" button to see the data from the source sheets transferred to the Follow Up sheet.
When you have some data in the Follow Up sheet, overwrite "Follow Up" in any cell in Column G with "Complete" then click away (or press enter or down arrow). You'll note that the relevant row of data is deleted.
I hope that this helps.
Cheerio,
vcoolio.
Hi vcoolio,
Thank you so much! I can see it is working great on your sample. However, when I tried to use this on my workbook it did not work. And when I tried to reverse engineer your sample I also couldn't get it working the way I want it to.
I think it may be because on each page I want the relevant data to start from the 9th row down. I.e. The First 8 rows will be frozen to make a header of sorts, with the first 7 displaying statistics for the day, the 8th displaying the headings, and the 9th row being where the data entry actually starts. All this I can do on my own.
When I have it set up in my desired layout, the error I get is: 'Run-time error '1004': AutoFilter of Range class failed', then it asks me to alter the following line of code when I debug: '.AutoFilter 7, "Follow Up", , , 7'
Can you think of a solution for this problem? Otherwise I am sure I can make it work with the code you have given me. I'll just have to tweak the layout a bit.
Thanks again for your help, I really appreciate it.
-- iksta
Thank you so much! I can see it is working great on your sample. However, when I tried to use this on my workbook it did not work. And when I tried to reverse engineer your sample I also couldn't get it working the way I want it to.
I think it may be because on each page I want the relevant data to start from the 9th row down. I.e. The First 8 rows will be frozen to make a header of sorts, with the first 7 displaying statistics for the day, the 8th displaying the headings, and the 9th row being where the data entry actually starts. All this I can do on my own.
When I have it set up in my desired layout, the error I get is: 'Run-time error '1004': AutoFilter of Range class failed', then it asks me to alter the following line of code when I debug: '.AutoFilter 7, "Follow Up", , , 7'
Can you think of a solution for this problem? Otherwise I am sure I can make it work with the code you have given me. I'll just have to tweak the layout a bit.
Thanks again for your help, I really appreciate it.
-- iksta
Hello Iksta,
The code slightly amended as follows should do the trick:-
The second code should still be OK as it is.
Test it in a copy of your actual work book.
Cheerio,
vcoolio.
The code slightly amended as follows should do the trick:-
Sub FollowUp()
Dim ws As Worksheet
Application.ScreenUpdating = False
Sheet1.UsedRange.Offset(8).ClearContents
For Each ws In Worksheets
If ws.Name <> "Follow Up" Then
With ws.[A8].CurrentRegion
.AutoFilter 7, "Follow Up", , , 7
.Offset(1).EntireRow.Copy
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
.AutoFilter
End With
End If
Next ws
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
The second code should still be OK as it is.
Test it in a copy of your actual work book.
Cheerio,
vcoolio.