PDA

View Full Version : Solved: keeping format when copying



blackie42
10-29-2007, 02:09 AM
Hi,

I'm copying & pasting entries from one book to another and my column 'C' is formatted to 4 decimal places. Problem is when it pastes the formatting gets lost.

Is there any code I can put in there (in the sheet module I expect) that will keep the formatting? I do have some other code in the sheet module that affects other columns.

thanks

Jon

Bob Phillips
10-29-2007, 02:17 AM
Look at PasteSpecial in help.

blackie42
10-29-2007, 03:21 AM
Yep - using pastespecial (all) as I want to keep the gridlines as well - doesn't seem to make a difference.

Bob Phillips
10-29-2007, 03:24 AM
gridlines are not part of formtting, they are a sheet attribute.

blackie42
10-29-2007, 03:37 AM
Sorry - its OK now - someone changed the formatting in the sheet to copy from which then carries fwd to the sheet to copy to.

regards

blackie42
10-29-2007, 06:37 AM
Hi again,

Found this code in one of my books

Sub test()
formatcolumns 2, 3
End Sub
----------------------------------------------------
Sub formatcolumns(first As Integer, number As Integer)
Dim i As Integer
For i = first To first + number - 1
Columns(i).NumberFormat = "0.####"
Next i
End Sub

I think this is formatting columns B,C & D to 4 decimal places - although being a novice I'm having trouble understanding how.

Can this be reworked so it only formats particular columns e.g col K, col T & col W

thanks v much

Bob Phillips
10-29-2007, 06:48 AM
It sets the columns starting at first for number of columns to the desiganted Excel format



Sub FormatColumns(ParamArray cols())
Dim i As Integer
For i = LBound(cols()) To UBound(cols())
columns(cols(i)).NumberFormat = "0.####"
Next i
End Sub

Sub TestFormatColumns()
FormatColumns "K", "T", "W"
End Sub

blackie42
10-29-2007, 08:30 AM
thanks a lot

Jon

lucas
10-29-2007, 08:34 AM
Hi Jon,
Be sure to mark your thread solved when you get a solution. Use the thread tools at the top of the page. You can always post followup questions after it is marked solved.