How can I automatically copy table to next sheet? Solved/Closed

Question

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





System Configuration: Windows / Chrome 65.0.3325.181

vcoolio · Answer

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.

vcoolio · Answer

Hi Daniel,

So you have headings in row 31 of each sheet" with data starting in row 32?

Cheerio,
vcoolio.

vcoolio · Answer

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.

vcoolio · Answer

Hi Daniel,

Other lines?

In which column does the data start in?

Cheerio,
vcoolio.

vcoolio · Answer

Ok. Change line 8 in the code to:-

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

vcoolio · Answer

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.

vcoolio · Answer

You're welcome Daniel.

I'm glad that I was able to help.

Cheerio,
vcoolio.

How can I automatically copy table to next sheet?

7 responses

Subscribe To Our Newsletter!