none
Macro to paste without conditional formating RRS feed

  • Question

  • I have a spreadsheet with 12 identically formatted tabs and I need a macro to insert a column in column f, moving the rest to the left, then copy the data from column e into f but without the conditional formatting...the only catch is that I need the colouring to remain?..
    
    I need it to do this for each of the tabs (named tab1 through tab12).
    
    Can this be done?

    Monday, June 15, 2015 8:59 PM

Answers

  • Hi,

    Are you goning to copy the values and the font color, without the cell format. you could try the following code, after copying data and cell format from E column to F column, it will reformat the cells in column F with the same interior color with cells in Column G. You may adapt it to your project.

    Sub CopyValue() Columns("F:F").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow Range("E1:E20").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("F1:F20").Select ActiveSheet.Paste

    'Reformat the cells For Each cell In Range("F1:F20").Cells If IsEmpty(cell) = False Then cell.Interior.Color = cell.Offset(0, 1).Interior.Color End If Next End Sub

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, June 24, 2015 7:08 AM
    Moderator

All replies

  • Sorry for writing in a code block, it wouldn't let me type any other way!

    i have tried to record a sub that copies each column into word and then copies it back to excel but it didn't work :(

    Monday, June 15, 2015 9:03 PM
  • Hi,

    >>I need a macro to insert a column in column f, moving the rest to the left, then copy the data from column e into f but without the conditional formatting

    you could try the below code:

    'insert the F column

    Columns("F:F").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow

    ' copy the Column E to Column F

    Columns("E:E").Select Selection.Copy Columns("F:F").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Hope it could help you.

    Best Regards,

    Lan

     


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 16, 2015 12:13 PM
    Moderator
  • Hi Lan, Thanks for your reply,

    the issue with that is that it will paste the values but not the cell colouring which is what i need to retain

    so if CF set in b1 that if b1=a1, colour red. i need to copy b1 to c1 pasting the value with the cell value as red BUT wihtout the CF.

    The reason is i need to keep track of the result with a pass of fail but the values in A1 will change month on month as its a target

    Thanks

    tom

    Wednesday, June 17, 2015 9:28 AM
  • Hi,

    Are you goning to copy the values and the font color, without the cell format. you could try the following code, after copying data and cell format from E column to F column, it will reformat the cells in column F with the same interior color with cells in Column G. You may adapt it to your project.

    Sub CopyValue() Columns("F:F").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromRightOrBelow Range("E1:E20").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("F1:F20").Select ActiveSheet.Paste

    'Reformat the cells For Each cell In Range("F1:F20").Cells If IsEmpty(cell) = False Then cell.Interior.Color = cell.Offset(0, 1).Interior.Color End If Next End Sub

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, June 24, 2015 7:08 AM
    Moderator