Newbie Needs Help

[Pat_The_Bat]

I am new to the forum, and while I considered myself an expert in Excel, now that I have started working with Macros and VBA, I am a humble beginner again!

What I'm trying to do is the follow...

Look at all cells in range B2:B100 and if B2 contains X, then copy the contents of C2 and add it to the next available empty cell on Sheet 2 Column C.

I'm starting some basic classes so I am getting the programming down at its basic form, but the syntax for this must be over my head a bit.
Any help would be appreciated.

 

[shg]

Sub PtBSlowButSimple()
  Dim cell As Range
  
  For Each cell In Worksheets("Sheet1").Range("B2:B100").Cells
    If cell.Value = "X" Then Worksheets("Sheet2").Cells(Rows.Count, "C").End(xlUp)(2).Value = cell.Offset(, 1).Value2
  Next cell
End Sub

Sub PtBFastButNotAsSimple()
  Dim rFind         As Range
  Dim sAdr          As String

  With Worksheets("Sheet1").Range("B2:B100")
    .Select
    Set rFind = .Find(What:="X", _
                       After:=.Cells(.Cells.Count), _
                       LookIn:=xlValues, _
                       LookAt:=xlWhole, _
                       SearchOrder:=xlNext, _
                       MatchCase:=True)
    If Not rFind Is Nothing Then
      sAdr = rFind.Address
      Do
        Worksheets("Sheet2").Cells(Rows.Count, "C").End(xlUp)(2).Value = rFind.Offset(, 1).Value2
        Set rFind = .FindNext(rFind)
      Loop While rFind.Address <> sAdr
    End If
  End With
End Sub

 

[Pat_The_Bat]

I tried the code. When I Run the macro, it appears to select column B, so it seems like it is going through it's steps. But nothing gets pasted to Sheet 2. The goal is to add any item with an X next to it to a running list on Sheet 2. I'm going to try to attach the workbook here if that's possible. Maybe you can see what I'm doing wrong. I'm so grateful for the help. I thought I responded previously but I must not have hit POST. I'm determined to figure this out and any direction that can be provided is soooooo appreciated!

 

[Fluff]

Will col B have anything other than an X?
Also is the X entered manually or by formula?

 

[Pat_The_Bat]

Will col B have anything other than an X?
Also is the X entered manually or by formula?

The X would be entered manually

Colomn B would not have any other data than someone typing the letter x.

at some point, maybe this becomes a check box that you click with mouse, but for now, putting an x in the box is the plan.

Thoughts?

 

[Fluff]

How about

Sub CopyData()
   With Sheets("Sheet1").Range("B2:B100").SpecialCells(xlConstants)
      .Offset(, 1).Copy Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1)
   End With
End Sub

 

[Pat_The_Bat]

How about

Sub CopyData()
   With Sheets("Sheet1").Range("B2:B100").SpecialCells(xlConstants)
      .Offset(, 1).Copy Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1)
   End With
End Sub

ALAS! Finally getting somewhere. The only problem I'm running into now, rather than using the next available cell in the new list on Sheet 2, it is still giving the blanks.

So this list

[TABLE="width: 248"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]x[/TD]
[TD]Boat [/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Car [/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Truck [/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Bus [/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]Hammock [/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

Looks like this: (Note there a blank row between bus and hammock)
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Boat [/TD]
[/TR]
[TR]
[TD]Car [/TD]
[/TR]
[TR]
[TD]Truck [/TD]
[/TR]
[TR]
[TD]Bus [/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Hammock [/TD]
[/TR]
[TR]
[TD]


[/TD]
[/TR]
</tbody>[/TABLE]
When really it should look like this: (No blank row between Bus and Hammock)
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Boat [/TD]
[/TR]
[TR]
[TD]Car [/TD]
[/TR]
[TR]
[TD]Truck [/TD]
[/TR]
[TR]
[TD]Bus [/TD]
[/TR]
[TR]
[TD]Hammock [/TD]
[/TR]
</tbody>[/TABLE]

 

[Pat_The_Bat]

I guess the other thing I should point out is this.
each time I click the command button, the list on sheet 2 should only update, rather than generating an entire additional list below the last cell of the previous list on sheet 2.
The idea is that page 1 is a master picklist, and the user can go through that list and put an X next to any document they want to see show up on that Sheet 2 list.
Then they click the command button, and it generates the list on Sheet 2. If the user comes back the next day and wants to add a couple more items to the list, they simply add x's next to the items on Sheet 1 (leaving all of the x's from yesterday as is), and hit the command button again. Now they have all the items on the Sheet 2 list that were there yesterday, plus any new item that was added.

Does that make sense? :/

 

[Fluff]

In future it would be better if you supply ALL the details in your OP rather than adding requirements at a later date.
You made no mention of blanks in col C (and if this is a pick list how do you pick ""?) & you're latest post is a complete change.
How about

Sub CopyData()
   With Sheets("Sheet1").Range("B2:B100").SpecialCells(xlConstants)
      .Offset(, 1).Copy Sheets("Sheet2").Range("C2")
   End With
   On Error Resume Next
   Sheets("sheet2").Range("C2:C100").SpecialCells(xlBlanks).EntireRow.Delete
   On Error GoTo 0
End Sub

This will overwrite whatever is in col C with the new list

 

[Pat_The_Bat]

In future it would be better if you supply ALL the details in your OP rather than adding requirements at a later date.
You made no mention of blanks in col C (and if this is a pick list how do you pick ""?) & you're latest post is a complete change.
How about

Sub CopyData()
   With Sheets("Sheet1").Range("B2:B100").SpecialCells(xlConstants)
      .Offset(, 1).Copy Sheets("Sheet2").Range("C2")
   End With
   On Error Resume Next
   Sheets("sheet2").Range("C2:C100").SpecialCells(xlBlanks).EntireRow.Delete
   On Error GoTo 0
End Sub

This will overwrite whatever is in col C with the new list

Fluff- This did the trick. I really appreciate the help, and I get what you are saying about the etiquette there. Will do a better job of that in the future.
-PB