Hello,
I've tried to make a VBA code to Copy and Paste column data in the same worksheet i.e from Column A to Column B with exactly the same format (Font, Alignment,Formulas etc.). Unfortunately, it only pastes data with formulas without alignment, font etc.
Does anyone know how to adjust that?
This is the code: Bold text should be adjusted for sure.
Dim sw, dw As Object
Dim ss, ds As Object
Dim excel, sheet As Object
Dim destination As Object
Dim xlUp as Integer = -4162
Dim RangeEnd as String
Dim xlPasteValues as Integer = -4163
Dim xlPasteValuesAndNumberFormats as Integer = 12
Dim xlPasteFormulasAndNumberFormats as Integer = 11
Dim xlPasteAllUsingSourceTheme as Integer = 10
Dim xlPasteColumnWidths as Integer = 9
Try
sw = GetWorkbook(Handle, Source_Workbook)
dw = GetWorkbook(Handle, Destination_Workbook)
ss = GetWorksheet(Handle, Source_Workbook, Source_Worksheet)
ds = GetWorksheet(Handle, Destination_Workbook, Destination_Worksheet)
sw.Activate()
ss.Activate()
excel = ss.Application
sheet = excel.ActiveSheet
'Get coordinate of last row in specified column
RangeEnd = sheet.Cells(sheet.Rows.Count, Range_End_Column).End(xlUp).Address
'Copy range
sheet.Range(Range_Start_Cell, RangeEnd).Copy
'Activate destination workbook and worksheet
dw.Activate()
ds.Activate()
sheet = excel.ActiveSheet
destination = sheet.Range(Destination_Range_Start_Cell).Activate
'Paste data as formulas
sheet.PasteSpecial(xlPasteFormulasAndNumberFormats)
sheet.PasteSpecial(xlPasteAllUsingSourceTheme)
sheet.PasteSpecial(xlPasteColumnWidths)
My.Computer.Clipboard.Clear()
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
sw = Nothing
ss = Nothing
dw = Nothing
ds = Nothing
excel = Nothing
sheet = Nothing
End Try
------------------------------
Lukas Simkus
Developer
Outokumpu Stainless
Vilnius
------------------------------
