Paste Special

[pbobel]

Hi,

I need to copy the format of one cell on one workbook and paste the format to column F of another Workbook. I keep getting an object error. My code is below. Any help is greatly appreciated.

Thanks,

Sub Macro2()
Dim RawFile
Dim ArchFile
        RawFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
        ArchFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")

    Set wkb0 = Workbooks.Open(RawFile)
    Set rng = wkb0.Sheets("Sheet1").Columns("C:K")

 With wkb0
'concatenate cells   
For i = 2 To lRow
    Cells(i, 3) = Cells(i, 1) & Cells(i, 2)
Next i
End With

 'concatenate cells 
 Set wkb1 = Workbooks.Open(ArchFile)
 With wkb1
For i = 2 To lRow
   Cells(i, 6) = Cells(i, 4) & Cells(i, 5)
Next i
 End with   
'**********************************************
' Copy and paste the special format
'**********************************************
[b]    wbk0.Range("C2").Select
    Selection.Copy
    wkb1.Sheets("Sheet1").Select
    Columns("F:F").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

[/b]

End Sub

 

[PHV]

Why not simply use the NumberFormat property ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pbobel]

Well I need the format to be exact on both spreadsheets because I will later use this to search for a match. I seem to be unable to get a match unless the format is identical. What is the best way to do this?

 

[SkipVought]

I seem to be unable to get a match unless the format is identical.

What kind of data are you umatching?

Please post an example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}