How can I automatically copy table to next sheet? [Solved]

- - Latest reply: vcoolio
Posts
1208
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 18, 2018
- Mar 28, 2018 at 12:42 AM
Hello,

I have an Excel file with 30 sheets containing the same table. In the table, the last column is a Yes/No (using data validation).
Once a day, I need to manually copy all the "No" rows to the next sheet.
According to what I saw on google, I need a macro to copy a range of cells to the next sheet - but only for the lines that present "No" in the last cell of the row.
I'll try to present it better below.
(No is the answer to the "reverted" cell)

Engineer | Action | Date of Action| Time of Action | Reverted
X | X | X| X | No
X | X | X| X | No
X | X | X| X | Yes
X | X | X| X | Yes
X | X | X| X | No

Basically I need to automatically copy all the rows that end with "No" to the next sheet.
Hope my request is understandable enough :)

Thanks ahead everyone..
Daniel





See more 

Your reply

7 replies

Posts
1208
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 18, 2018
0
Thank you
Hello Daniel,

Could you please clarify your opening post a little more.

By "next sheet" do you mean a summary sheet?
Do you need all rows with "No" in Column E from each sheet to be transferred to a summary sheet?

Cheerio,
vcoolio.
Hey, thanks for the response!

It's a worksheet with 31 sheets on it, I'm filling new information every day. So I'm copying all the information with "No" in column L (lines 32-65) to the next sheet (next day).
All the 31 sheets are empty (until we get to the day), and at the end of the day, all the rows that answer "No" needs to be copied to the next day. (All the sheets name's are numbers between 1-31).
The information should be filled in lines 31-65, based on the first empty line.

Let me know if you need more information..
Thanks!
Respond to vcoolio
Posts
1208
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 18, 2018
0
Thank you
Hi Daniel,

So you have headings in row 31 of each sheet" with data starting in row 32?

Cheerio,
vcoolio.
Yep, the table's first row is 31 containing the "headlines", and the data is starting in row 32 to 65.
Respond to vcoolio
Posts
1208
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 18, 2018
0
Thank you
Hi Daniel,

Try the following code assigned to a button (on each sheet):-

Sub CopyData()

Application.ScreenUpdating = False

With ActiveSheet.[A31].CurrentRegion
                .AutoFilter 12, "No"
                .Offset(1).Copy
                ActiveSheet.Next.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
End With

Application.ScreenUpdating = True
Application.CutCopyMode = False
ActiveSheet.Next.Select

End Sub


It will operate from the active sheet (on whichever day) and transfer all relevant rows of data with a "No" in Column L to the next sheet.

I hope that this helps.

Cheerio,
vcoolio.
Thanks very much for the help!
It does copy the rows to the next sheet, but to other lines - I need it to move to line 32 and forward.
Also, is there a way to apply it for the whole sheet and it will update automatically once a change is made? or do I have to apply it each time I want to copy the info?
Respond to vcoolio
Posts
1208
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 18, 2018
0
Thank you
Hi Daniel,

Other lines?

In which column does the data start in?

Cheerio,
vcoolio.
The data is showing up in the next sheet starting line 16 column B.
The data starts in column B, so that would make B32 as the first data cell.
Respond to vcoolio
Posts
1208
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 18, 2018
0
Thank you
Ok. Change line 8 in the code to:-

ActiveSheet.Next.Range("B" & Rows.Count).End(3)(2).PasteSpecial xlValues


Cheerio,
vcoolio.
Now it's starting from line 32 (correctly) but from column C lol
Respond to vcoolio
Posts
1208
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 18, 2018
0
Thank you
Sorry Daniel,

Another couple of changes are necessary with the last change.

Change line 5 to:-

With ActiveSheet.[B31].CurrentRegion


and line 6 to:-
.AutoFilter 11, "No"


Cheerio,
vcoolio.
Now it works great, thank you very much! :)
Respond to vcoolio
Posts
1208
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
December 18, 2018
0
Thank you
You're welcome Daniel.

I'm glad that I was able to help.

Cheerio,
vcoolio.
Respond to vcoolio