How do I get the formatting as well as values?

Question

How do I get the format and fill of a cell as well as values? I am transferring some data from one work sheet into another and need the formats but this code only takes the values. Any suggestions please?

Dim i As Integer
Dim j As Integer
Dim r As Integer
Dim l As Integer
Dim c As Integer

r = 2 'row No to start at on original data
l = 2 'row to start at for Mitigation

Sheets(2).Activate
Range("D2").Select
j = 2 ' row

Do Until IsEmpty(ActiveCell)  ' Set Do loop to stop when last row is reached.
    c = 4 ' column
    Do Until IsEmpty(ActiveCell)  ' Set Do loop to stop when an empty cell is reached.
        Sheets(6).Cells(l, 3) = Sheets(2).Cells(j, c) 'Description
        Sheets(6).Cells(l, 4) = Sheets(2).Cells(j, 1) 'H ID
        Sheets(6).Cells(l, 5) = Sheets(2).Cells(j, 2) 'S ID
        ActiveCell.Offset(0, 1).Select
        c = c + 1
        l = l + 1
    Loop
    r = r + 1
    j = j + 1
    ActiveCell.Offset(1, 4 - c).Select
Loop

This might help: https://stackoverflow.com/questions/25461314/vba-copy-cells-value-and-format — PKatona, Jan 18 '18 at 15:16
And this might help [tremendously] improve the performance: https://stackoverflow.com/q/10714251/1188513 — Mathieu Guindon, Jan 18 '18 at 15:39

ChipperG · Answer 1 · 2018-01-18T16:35:08.577

0

you might try to do a copy/paste:

Sheets(2).cells(j, c).Copy Destination:=Sheets(6).Cells(l, 3)

This code would copy the cell in row "j" and column "c" on sheet 2, and paste it to sheet 6, row "l" column "3"

edited Jan 18 '18 at 16:35

answered Jan 18 '18 at 15:18

ChipperG

64
1
5

this just deleted all the formats in the sheet that I was copying from! Luckily I had a copy. – Dawn Jan 18 '18 at 16:25
Good to hear - Please accept solution if it works for you? – ChipperG Jan 18 '18 at 16:53

How do I get the formatting as well as values?

1 Answers1