copy values from 1 sheet to another

[abhi900]

On sheet 1 I have 4 cols and the 5th col contains 2 values ("Name of the resource" and "TBC").

So what I want is > I need these 4 cols(from sheet1) to be replicated in sheet 2 BUT only based on "TBC".

Hence if out of 50 rows in sheet 1 If I have 10 TBC, then in SHEET 2 I want to see just these 10 values.

Makes sense ?

Tried this VBA but cannot make it work..
Private Sub CommandButton1_Click()
LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
For i = 1 To LastRow
If Range("TBC" & i).Value = 1 Then
LastRow2 = Sheets("Sheet2").Range("A65536").End(xlUp).Row + 1
Sheets("Sheet2").Range("A" & LastRow2).Value = Sheets("Sheet1").Range("A" & i)
End If
Next i

Sheets("Sheet2").Range("A1").Delete Shift:=xlUp

End Sub

 

[SkipVought]

hi,

On sheet 1 I have 4 cols and the 5th col contains 2 values ("Name of the resource" and "TBC").

This statement here...

  If Range("TBC" & i).Value = 1 Then

means if Column TBC, row i (whatever value is stored in the variable i) If that cell in Column TBC EQUALS 1 then...

I don't think that's what you intend, based on your statement I quoted above.

If that's is the case, then maybe the statement ought to be...

  If Cells(i, 5).Value = "TBC" Then

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[TheAceMan1]

How are ya abhi900 . . .

I believe Skip is right. If not could you post sample data for the [blue]5th col[/blue].

[blue]   Dim wb As Workbook, ws1 As Worksheet, ws2 As Worksheet
   Dim LastRow As Long, row As Long, col As Long
   
   Set wb = ThisWorkbook
   Set ws1 = wb.Worksheets("Sheet1")
   Set ws2 = wb.Worksheets("Sheet2")
   LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).row
   
   For row = 1 To LastRow
      If [purple][bold]Cells(row, 5).Value = "TBC"[/bold][/purple] Then
         For col = 1 To 4
            ws2.Cells(row, col).Value = ws1.Cells(row, col).Value
         Next
      End If
   Next
   
   Set ws2 = Nothing
   Set ws1 = Nothing
   Set wb = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading!
Also FAQ181-2886 Worthy Reading!