CODEWITHSUNDEEP
excelvbaformattingcopy-paste
JohnM
JohnM

Reputation: 37

VBA Code - Retain Formatting for data copied to different sheet

As a new user of VBA in Excel I'm trying to complete the following:
1) Check multiple rows of one column in a sheet called "Index" for a condition (My condition is to look for the letter Y in cells in Column J)
2) Copy selected Columns from each row where the condition is satisfied to another sheet as programmed in the VBA Code, e.g. "Sheet2"
3) Retain Formatting of cells that are copied, i.e. Formulas and Bold Font in particular.

I asked a question previously at VBA code to copy selected columns from rows that meet a condition to another sheet and was kindly given the following code by ATl LED that works and copies the values but not the formatting (I forgot to include this in my question)

I experimented with the code and tweaked .Value to .FormulaR1C1 in the IF Statement. The formulas are copied and work a treat in the sheet copied to, i.e. Sheet2 but I can't get the formatting to work.

What am I missing?

Sub try3()
Dim i, x As Long
Dim Y as String
Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("Index")
Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Sheet2")
x = 5
Y = "Y"
For i = 2 To 500:
If ws1.Cells(i, 10) = Y Then
   Range(ws2.Cells(x, 1), ws2.Cells(x, 7)).Value = Range(ws1.Cells(i, 3), ws1.Cells(i, 9)).Value
  x = x + 1
End If
Next i
End Sub

Thanks, JohnM

Upvotes: 1

Views: 6638

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Place the following line of code:

Range(ws1.Cells(i, 3), ws1.Cells(i, 9)).Copy Range(ws2.Cells(x, 1), ws2.Cells(x, 7))

instead of your line:

Range(ws2.Cells(x, 1), ws2.Cells(x, 7)).Value = Range(ws1.Cells(i, 3), ws1.Cells(i, 9)).Value

Or keep both in the same order if in ws1 sheet are any formulas which you don't want to copy (but values).

Upvotes: 1

Related Questions