Last 8 columns to export to a new sheet

[kishoremcp]

Hi,

I have a sheet with name "new updates" in which I will update columns data daily. I want a macro which will export the first column and last 8 columns to a new workbook with name "updates_august_week3"(current month_current week in that month). The last 8 columns should be count based on row 2.

Please help me.
Please note that there are some hidden rows in the sheet 8 to 14

Attached is the reference workbook.

Thanks in advance.

[kishoremcp]

any help please

[kishoremcp]

I Tried with the below macro but not working...any modification help please

Please Login or Register  to view this content.

[kishoremcp]

any help please..

[Fotis1991]

Just because i see your bumps for response i'd like to tell you that if you will not find any VBA solution and if you are interest we could do this using formulae.

[kishoremcp]

I am sorry. it will be helpful if you could give me a macro. if not i will try with a formula. but i dont think that it will completely helpful for me.

[ragulduy]

In your code you need to change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[kishoremcp]

it is working. can i have the same color formatting also to copied to the destination? yudlugar

[ragulduy]

Maybe:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

Do you mean a new workbooks or worksheet? I created a sheet1 manually to test.

[kishoremcp]

its working...just one doubt....will it copy the same way if the cells have formulas which dont reflect any value.

[kishoremcp]

how do i paste them as values with color formatting yudlugar

[ragulduy]

I'd guess you'd have to do it separately, I don't think you can paste color formatting separate to all the other formatting though.:

Please Login or Register  to view this content.

[kishoremcp]

Okay. Thanks

[kishoremcp]

Need some more help..

I have the code which is working which is posted above...

Please Login or Register  to view this content.

There is a small problem. I have formulas in cells which are not reflecting any value. (I mean cell has no value but formula in it).
So, the above macro is taking the last 8 columns which has formulas also with no values.

Can anybody modify the macro so that it should take only if values are there.

[ragulduy]

Do you want the last column with a value and the preceeding 7 columns or the last 8 columns with a value in (i.e. skip any blanks)?

[kishoremcp]

I want the last 8 columns which has values only. But not formulas.

[ragulduy]

Maybe something along the lines of:

Please Login or Register  to view this content.

[kishoremcp]

application defined error or object defined error is coming at line 4 in the posted macro

[ragulduy]

I put the bracket in the wrong place
count_col = .Range(.Cells(1, Columns.Count)).End(xlToLeft).Column
although probably can be simplified to
.cells(1,columns.count).end(xltoleft).column

[kishoremcp]

object does not support this property or method.. if i put .cells(1,columns.count).end(xltoleft).column and the same previous error if i put count_col = .Range(.Cells(1, Columns.Count)).End(xlToLeft).Column

[ragulduy]

Ok I've tested this one properly and can't find any issues:

Please Login or Register  to view this content.

[kishoremcp]

it is still copiying those columns with formulas which dont have any values

[ragulduy]

Can you define "no values"? Preferably by uploading a workbook with an example?

Note - the macro I wrote will look in row 1 for the value, so if the header still exists but there is no data in the table it will still copy. It assumes that if the column has no values, row 1 of that column will also have no value.

It also assumes that no value means "", not 0.

[kishoremcp]

Yes, I will upload the sample again..Thanks for your help really...I changed in the macro to look in row 2.

[ragulduy]

That file has all values and no formulas. I still do not understand what you mean by "has no value". Can you upload a workbook with an example of a column that has formulas but you do not want copying.

On your example, if I enter ="" into column S it will still copy columns K to R which I think is what you want?

[kishoremcp]

In the previously attached file the new updates sheet in range from i2 to i5 has formulas. so if you run the macro it is copiying that column as well eventhough it dont have any values. I hope you got my point this time.

[kishoremcp]

Sorry in S2 to S5 has formulas

[ragulduy]

There are no formulas in S2 to S5 in the new file you uploaded. When I run the macro on the new file you uploaded it does not copy column S. Please try again to upload a file with example or macro not working (e.g. formulas in column S)

[kishoremcp]

Okay...Please find the file again. I am highlighting the cells with formulas in yellow color in new updates sheet.

[ragulduy]

It didn't work because you still have the header in column S, you needed to change one of the other statements to row 2 also:

Please Login or Register  to view this content.

[kishoremcp]

Okay..Got it. Thanks!! It worked for me...