Macro to backup data in separate sheet

[SubwAy]

Hi folks,

I'm having some problems writing a macro to backup a range on a hidden sheet (changing in length) onto another hidden sheet.

I have attached an example of what I'm trying to achieve:

• copy all lines with content other than "" from columns A to C in "Live" sheet (you'll note that this sheet imports from another one hence the <>"")
• paste values only in the next available line on sheet "Backup", starting in column C
• tricky part that I don't know how to go about: insert in column B for the range that has just been pasted the start of the current week date in the format "dd-mmm" as text and in column A concatenante this date and the content of the next column.

Please Login or Register  to view this content.

Can anyone help me with this?

Thanks,
S

[spitfireblue]

Maybe like this?

Please Login or Register  to view this content.

[SubwAy]

I may be doing this wrong but nothing happens at all when I run the code

[SubwAy]

Hi spitefireblue,

So I was obviously doing something wrong It does copy the data but it also copies the blanks (if you look on Sheet "Live" I have a formula that returns blank and the macro is selecting this and copying it.

Also, the date and concatenate do not appear at all, any idea?

[SubwAy]

Ok I'm a moron I had a selection going in the activesheet (as I was watching what was happening) and it stuffed everything.

So, the only little glitch is that the date does not get entered as text "4-Jul" but as 4/07/2016 formatted as "4-Jul". Is there any way to change this so I have it as a text rather than a date? Also, I can't see "Date" being clearly defined so I assume that tomorrow this will change to 7-Jul, yet I need to capture the first day of the current week only. Can this be done?

[spitfireblue]

Hi Subway,
To format the dates as text, format Column B as text and use the same code.
To generate a start of week date use this ammended code.

Please Login or Register  to view this content.

[SubwAy]

Hi spitfireblue,

Thanks for spending time on this. It works perfect in my actual workbook with the exception of one thing: the two worksheets (backup and live) are hidden therefore I get a "select method of worksheet class failed" error. Is there a way to address that or do I need to abslutely have my sheets visible?

For info, the code I now have is:

Please Login or Register  to view this content.

[spitfireblue]

You have to make your sheets visible. But you can do that in your code...

Please Login or Register  to view this content.

and then hide it again at the end

Please Login or Register  to view this content.

[SubwAy]

You sir, are a very smart cookie. thank you