Different Language Has Different Character Widths

[abraham30]

Hi,

I want to copy the data from selected row (suppose R1 to R10) from sheet "Data1" and then paste into another sheet "Data2", the headers are not being populated properly.

As the fonts in the header are in some other language, the original requester want the header to be exactly same (without any space) to the copied one which I am not able to do.

e.g. I copied the data from R1-R10 and paste in other sheet "Data2". -->i) Header in column c are not populated exactly .
ii) Header in column I of sheet Data1 is more widened compared to col I of data2.

Is it possible to develop macro which will keep the exact copy to copied sheet. (See attached sheet)

Advance thanks

[SamT]

Abraham,

I changed the title to attract more responses.

Have you tried the macro on the requester's computer. It may just be that it is responding to the default language on your computer and will work fine on his/hers.

[EirikDaude]

I'm not sure if I understand exactly what you are looking for here, but if what you want is to keep the column widths in the second sheet, couldn't you use something like "Paste special" -> "Column widths"?

Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

[Paul_Hossler]

Maybe like this?

Option Explicit
Sub Macro1()
    Dim rSource As Range, rDest As Range
    Dim iColumn As Long
    Set rSource = Worksheets("Data1").Cells(1, 1).CurrentRegion
    Set rSource = rSource.Cells(1, 1).Resize(10, rSource.Parent.Columns.Count)
    
    rSource.Copy
    
    Worksheets("Data2").Cells(1, 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    Set rDest = Worksheets("Data2").Cells(1, 1).CurrentRegion
    
    
    For iColumn = 1 To rSource.Columns.Count
        rDest.Columns(iColumn).ColumnWidth = rSource.Columns(iColumn).ColumnWidth
    Next iColumn
End Sub

Paul