Copy a row to a separate sheet with a condition

**malitec** · 10-07-2014, 05:48 AM

Hi guys

Requesting your guidance to copy the cells (B:I and K) in each row in sheet " 7 Jan 14 " to cells B:J in sheet " 14 Jan 14 " when the text in K is " Still In Care", which is the condition. Please note the condition in "K" in sheet " 7 Jan 14 " is not copied to "K" in sheet " 14 Jan 14 " but it should be copied in to cell J instead. Likewise when the text in K is " Still In Care", when entered in to sheet " 14 Jan 14 ", the cells (B:I and K) should be copied to the next sheet which is " 21 Jan 14 ".

I will have 52 sheets which indicates the 52 weeks for the year 2014. The above said code should be applied to all the 52 sheets. ( The attached excel book has got only 2 sheets.I am more than happy to upload a complete work book with 52 sheets if that helps.)

Looking forward for your valuable guidance.

Thanks

Malitec

**p24leclerc** · 10-07-2014, 03:40 PM

Would you mind put some data in your sheets so we see what you want to be copied? I suppose you only want to copy rows starting at row 9 and below.
Also, what do you mean by "the code should be applied to all 52 sheets"? Can you detail your process a little bit more?
Regards

**malitec** · 10-08-2014, 03:59 AM

Hi p24leclerc

Apologies for not giving the details. As you said I have to copy rows from 9 onwards only.

I have uploaded a new workbook with data. As an example I have manually copied the data from sheet "7 Jan 2014" and paste it in "14 Jan 2014". You will notice the text in cell "K" of "07 Jan 2014" worksheet is copied to cell "J" in "14 Jan 2014". Rest of the data from "04 Jan 2014" are copied in to the same cells in "14 Jan 2014".

My final work book will have 52 work sheets, one for each week of the year. That is why I said when you write the code please ensure the VBA can be applied to all 52 sheets.

Hope I am clearer this time

Regards

Malitec

**p24leclerc** · 10-08-2014, 11:41 AM

here is a macro you could try. It could be run from any sheet and will copy data on a sheet's name defined by the actual sheet name (which should be a date in a format suitable for the DateValue function) with 7 days added to it.

Please note that I had to reposition a cell in column B into column C because the macro uses a function to locate the last entry in column B and that this TITLE was in the way. You can place it where you want except in column B.

Please Login or Register  to view this content.

Hope this help

**malitec** · 10-12-2014, 09:00 PM

Hi

Thanks for the macro. Also please accept my apologies for the delayed reply.

I have tried the macro but it seems something is preventing it from giving me the results. I am not at all good in macros. Please check the macro I have added to this work book.

I have attached a 52 sheet workbook as a zip file for your perusal. Would you mind checking the macro in this work book and advice me where I went wrong.

Thank you

malitec

**p24leclerc** · 10-13-2014, 01:34 PM

Hi,
First thing is to put this macro in a Module. Actually it is at the sheet level and this means it is not available to the other sheets. You want to place it in a Module (any one) so that you will be able to call it form anywhere in the workbook. DO NOT FORGET TO ERASE IT AT THE SHEET LEVEL.

Secondly, the macro was modified and you made some mistake doing it. Here is a version that works in your workbook:

Please Login or Register  to view this content.

There were 2 main errors:
1. The variable name WS2_Name was changed to WS2_ "14 jan 2014"
2. there was an "End IF" statement left when you deleted some part of the macro.

I saw you deleted the part that is checking if the next week sheet exists. It's up to you to do so but if for some reason the macro does not find the next week sheet, you'll get an error meesage and it will not as obvious as the message box the macro was displaying when it happens. I'd rather leave it there.
Hope this helps.

**malitec** · 10-15-2014, 08:31 PM

Hi

I tried but failed to get the result. And I am unable to work out the issue that hindering the macro function.

If time permits, could you please try the macro in to the 52 weeks work book (zip file) I have attached earlier and let me know the problem.

Thanks in advance

Regards

malitec

**p24leclerc** · 10-16-2014, 08:47 PM

Here is your workbook with the macro in Module1. It workslike this:
When in any sheet, you run the macro named Transfer_Data which will take the sheet name, convert it in a date, add 7 days to this data and come up with the next sheet name. It then scan all rows in the active sheet and copy those that have "Still In Care" in column K to the next sheet as required.
Hope this will work for you. If not, then ask again for help.
Regards

**malitec** · 10-26-2014, 05:42 AM

Hi

Could not log on to the forum due to some personal issues. Thanks for the reply. May be because of my inexperience with VB the macro is not working. Anyway thanks a lot for your effort. If time permits please have a second look at the request.
Thanks

**p24leclerc** · 10-26-2014, 02:02 PM

Check if your Excel is configured to accept to run macros. By default, it is not. You have to change it in the Files/Options/Trust Center/Trust Center Settings/Macro Settings menu.

Copy a row to a separate sheet with a condition

LinkBack

Thread Tools

Rate This Thread

Display

Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Re: Copy a row to a separate sheet with a condition

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA, Intercept 2 giving range from 2 sheet,If condition, intercepted copy to 3rd sheet

Getting rows from a separate sheet that matches a condition

Copy& paste the data one sheet to another sheet if the condition is true

[SOLVED] Copy rows from active sheet to another sheet (last row) if condition in column X is met

[SOLVED] VBA for separate sheet with particular condition

Bookmarks

Bookmarks

Posting Permissions