Keep source formatting in vba

[Shadkng]

Hi, in the below code if possible I would kike to have the source font color / style, such as red or bold" remain when being copied from sheet "master" to sheet "new invent". Thanks for the help.

Worksheets("MASTER").Activate

Range("A1:H1000").Copy Destination:=Sheets("NEW INVENT").Range("A1")
Range("A1:H1000").Copy
Sheets("NEW INVENT").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

 

[6StringJazzer]

Why are doing the copy twice? The first copy should paste everything including source formats.

 

[Shadkng]

Why are doing the copy twice? The first copy should paste everything including source formats.

I didn't realize that. But I'm having trouble fixing for some reason and keeping the formatting. Would you be able to correct? Thanks

 

[6StringJazzer]

Try it with just this code and let us know what it does.

Worksheets("MASTER").Activate

Range("A1:H1000").Copy Destination:=Sheets("NEW INVENT").Range("A1")

 

[Shadkng]

Your code does copy the formatting, but I have other macros following it and my font color goes from red back to black. I think it's happening in the code below which runs next, but I don't know why.

Sub Text_To_Col()

    Worksheets("NEW INVENT").Activate
    Range("A5:A1000").Select
  
    Selection.TextToColumns Destination:=Range("C5"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="-", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 2)), TrailingMinusNumbers:=True
        
        Columns("A:B").Delete
        Range("E:E").NumberFormat = "0"
        
        Range("A:C").HorizontalAlignment = xlCenter
        Range("D:D").HorizontalAlignment = xlLeft
        Range("E:F").HorizontalAlignment = xlCenter
               
End Sub

 

[6StringJazzer]

Formatting is not carried over when you do Text To Columns. Maybe do this after the text to columns. I have not tested this yet.

Range("A1:H1000").Copy
Sheets("NEW INVENT").Range("C1:F1").PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False

 

[Shadkng]

I will try thanks.

 

[6StringJazzer]

I assumed the result of your text to columns would be in C:F

 

[Shadkng]

I assumed the result of your text to columns would be in C:F

I'm not clear how I can run that code after text to columns. Once text to columns is run why would I copy again?

 

[6StringJazzer]

Because the second time you are just copying and pasting the formats only, to the new columns created by TTC.

Sheets("NEW INVENT").Range("C1:F1").PasteSpecial Paste:=xlPasteFormats