1

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

Community
  • 1
  • 1
JohnM
  • 37
  • 1
  • 2
  • 9

1 Answers1

1

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).

Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
  • Hi Kaz Jaw, Thanks for your suggestion. I've replaced my line with your one above and it removes the values that I am trying to copy in Index. Nothing appears in Sheet2. What could be causing that? – JohnM Mar 15 '13 at 18:03
  • Please check again if you have correct order. In my line there is `ws1` on the left site and `ws2` on the right. While it is just the opposite in your line. – Kazimierz Jawor Mar 15 '13 at 18:07
  • D'oh! Thanks - that works :-). One last thing - there are blank rows in ws2. How can I remove those so that the copied data is displayed over consecutive rows? – JohnM Mar 15 '13 at 18:14
  • Is ws1 continuous data range? Are there any blanks in ws1 which you copy? I'm trying to figure out why you have blanks in ws2... – Kazimierz Jawor Mar 15 '13 at 18:17
  • Yes - ws1 is a continuous Range. The exact amount of rows that don't have a Y in ws1 are left as blank rows in ws2 – JohnM Mar 15 '13 at 18:22
  • So, check if you have `X=X+1` just before `End if` – Kazimierz Jawor Mar 15 '13 at 18:25
  • The only idea I have, sorry for that, is that you mixed up `x` and `i` variables somewhere in the code. – Kazimierz Jawor Mar 15 '13 at 18:30
  • ok - i'll double check the variables and step through the code to see what happens. Thanks – JohnM Mar 15 '13 at 18:33
  • Sorted - When I swapped the ws1 and ws2 in your suggested line i needed to swap the x and i variables. Thanks, John – JohnM Mar 15 '13 at 18:42