Adapt code to carry over formatting as well

[Kirk^]

Hi Folks,
Alpha Frog put this code together for me and it works a treat however it doesn't retain the formatting of the copied cells.

Can some one show what needs to be changed to include the formatting of the copied cells in the code below.

Sub test()

    Dim wbMaster As Workbook
       
    On Error Resume Next
        Set wbMaster = Workbooks("Master Books 2011 - 2012 Q1.xlsm")
    On Error GoTo 0
    If wbMaster Is Nothing Then
        Set wbMaster = Workbooks.Open(Filename:="F:\Master Books 2011 - 2012 Q1.xlsm")
    End If
    
    With wbMaster.ActiveSheet
        .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
        .Range("A2:AH2").FormulaR1C1 = "='[" & ThisWorkbook.Name & "]DATA STORAGE'!R3C"
    End With
    wbMaster.Close SaveChanges:=True
    
    Range("B3").Select
    
End Sub

Look forward to your responses.

Many thanks
Kirk^

 

[chuckchuckit]

When not copying using the clipboard, such as using the "With" procedure, each format type needs to be also transfered besides the value. That can get quite extensive. I have not found an easy way to do so. So I usually copy the format type I need in each case.

 

[chuckchuckit]

Here is a discussion we had a while back about your same question:

http://www.mrexcel.com/forum/showthread.php?t=503224

 

[Kirk^]

Hi chuckchuckit

Thank you for the prompt reply.
So correct me if I am wrong. Are you telling me you know of no way to put a simple one piece code instruction in that will instruct the formatting to be carried over?

Are there no changes that can be made to this piece of code

CopyOrigin:=xlFormatFromLeftOrBelow

to make it work. This is where I thought i may be able to acheive a result.

Thanks
Kirk^

Hold on reading other post..

 

[Kirk^]

Hi again chuckchuckit

I read the other post and see that this is a tricky one....
If anyone else has any insights into a resolution for this curly one I think both myself and chuckchuckit would love to hear from you

Thanks again
Regards
Kirk^

 

[chuckchuckit]

If

CopyOrigin:=xlFormatFromLeftOrBelow

copies all the formats for you and you like that better than using "With" that might be a go. But likely it will be using the clip board to copy. Using clipboard Like Ctrl-C or any of the VBA Copy commands will all likely copy the formatting too.

But there are problems using the clipboard for copying. If you are just starting coding learning curve, I would put the learning into using "With" type procedures for copying. As the clipboard becomes very suseptable to becoming corrupted or erased when you start to do more complicated and automated type things in Excel. Clipboard use can be a nightmare with those types of programs.

I started off using the clipboard and regretted it sorely as I then had to learn the "With" etc and using the different format paramaters too.

All IMO but you might want to ask others too if you are indeed just starting that famous "learning curve..."

Not trying to be discouraging, as is just IMO. All depends upon your uses one might say.

In any case VBA is a lot of fun.

Best to you Kirk.

Chuck

 

[Kirk^]

Thanks chuckchuckit,
Point taken

Cheers
Kirk

 

[chuckchuckit]

Kirk,

It is lights out for me now, but later if you would like to know the list of parameters, I can dig them up for you if you consider going that way. Or someone here may have a complete list handy. They are just simply added to any "With" statement, that you want to transfer that particular parameter along with the value.

Example being where you have inside "With"

        .Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow
        .Range("A2:AH2").FormulaR1C1 = "='[" & ThisWorkbook.Name & "]DATA STORAGE'

You would simply usually be adding to the "With" code desired formats like:

        .Font.Color
        .Interior.Color

etc. You usually do not need to do every format there is, just the ones needed, if any other than .Value.

Using clipboard copying is very slow. Using "With" is lightening fast. That can become a factor also.

Lights out...

Chuck

P.S. It is also good to stay away from using .Select for the same reason as using the Clipboard and Copy commands. .Select can easily become "unSelected" by other running instances of Excel or even other programs running on your desktop. The "With" does not have a problem with using .Select because it does not use it.

 

[Kirk^]

I would certianly like to see the list and an example of how it coded for sure.
Some things work well while others I need to be a little more precise with I guess.

Thanks again

Regards
Kirk

 

[chuckchuckit]

Kirk,

Here is an example to try. Type something into cell A1 (your souce cell). Change cell A1 formats to different alignments and different colors, both font color and fill color. Then run this code and you will see the format copy along with it.

There are a lot of more formats which you can research to add if you need them. One way is when you type Range("A1").Font. as soon as you type the 2nd dot a box will pop up showing you all the different formats possible. Likely you will not need to copy them all, but there they will be.

Using the "With" way to copy cells and ranges is a much faster and more problem free than using the clipboard to copy. Just takes a bit to learn at first.

Sub CopyFormatsNoClipboard()
 
    Dim SourceLoc, DestinationLoc
        SourceLoc = "A1"
        DestinationLoc = "C3"
 
    With Range(DestinationLoc) 'Destination
            'Source value and some formats
        .Value = Range(SourceLoc)
        .Interior.Color = Range(SourceLoc).Interior.Color
        .Interior.Pattern = Range(SourceLoc).Interior.Pattern
        .Font.Color = Range(SourceLoc).Font.Color
        .Font.Size = Range(SourceLoc).Font.Size
        .HorizontalAlignment = Range(SourceLoc).HorizontalAlignment
        .VerticalAlignment = Range(SourceLoc).VerticalAlignment
    End With
 
End Sub

Chuck