Copy and Paste
 

Is there a way to make a macro / command button that selects all the
values in a column only if they contain information and paste it
somewhere else?

So say you have a column that has 4 cells in it.

cell 1 has: "Hello"
cell 2 is: empty
cell 3 is "3"
cell 4 is "Bye"

When you run the macro it will only copy Cell 1, Cell 3, And Cell 4 and
paste those into another cell.

Copy and Paste
 

The following will go down 20 rows (you can change the "20" to whatever you
need). If the target cells are not in the same order, it will be a bit more
complex.

Sub cp()
Sheets("Sheet1").Activate
Range("A1").Activate
For x = 1 To 20
If ActiveCell < "" Then
ActiveCell.Offset(0, 2) = ActiveCell
ActiveCell.Offset(1, 0).Activate
Else
ActiveCell.Offset(1, 0).Activate
End If
Next
End Sub
--
Best wishes,

Jim


"RigasMinho" wrote:

Is there a way to make a macro / command button that selects all the
values in a column only if they contain information and paste it
somewhere else?

So say you have a column that has 4 cells in it.

cell 1 has: "Hello"
cell 2 is: empty
cell 3 is "3"
cell 4 is "Bye"

When you run the macro it will only copy Cell 1, Cell 3, And Cell 4 and
paste those into another cell.

Copy and Paste
 

This code will copy values from Sheet1 column 1 and paste them into
Sheet2 column 1 without the empty cells. Change lngfindcells from 60 to
whatever you need. If this doesn't work for you or you need something
different, let me know.

Dim lngfindcells As Long 'Set counter up for Sheet1
Dim lngdestcount As Long 'Set counter up for destination cells in
Sheet2

'Set counter = 1 so destination values on Sheet2 begins at row 1
lngdestcount = 1

'Cycle through every cell in Sheet1 column A, starting at row 1
For lngfindcells = 1 To 60

If Worksheets("Sheet1").Cells(lngfindcells, 1) = "" Then
'Do Nothing
Else
'If cell on Sheet1 has a value, copy it and paste it into
Sheet2, beginning in column A, row 1
Worksheets("Sheet1").Range("A" & lngfindcells & ":A" &
lngfindcells).Copy Destination:=Worksheets("Sheet2").Range("A" &
lngdestcount)

'Increment counter so next value copied will be below
previous one on Sheet2
lngdestcount = lngdestcount + 1
End If
Next


--
kev_06
------------------------------------------------------------------------
kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
View this thread: http://www.excelforum.com/showthread...hreadid=561602

Copy and Paste
 

That seems to work better for me.

How would I make it so that it cylces through and only finds values
with "A" in them.

I have this line but doesnt seem to work.

Worksheets("Master Questions").Range("C" & lngfindcells & ":C" &
lngfindcells).Cells.Find("A").Offset(0, -1).Copy
Destination:=Worksheets("Output").Range("A" & lngdestcount)

- So basically it will search Column C and find a cell with value "A"
- take the cell to the left of that value and paste it into worksheet
output.

Having a difficult time with this.


kev_06 wrote:
This code will copy values from Sheet1 column 1 and paste them into
Sheet2 column 1 without the empty cells. Change lngfindcells from 60 to
whatever you need. If this doesn't work for you or you need something
different, let me know.

Dim lngfindcells As Long 'Set counter up for Sheet1
Dim lngdestcount As Long 'Set counter up for destination cells in
Sheet2

'Set counter = 1 so destination values on Sheet2 begins at row 1
lngdestcount = 1

'Cycle through every cell in Sheet1 column A, starting at row 1
For lngfindcells = 1 To 60

If Worksheets("Sheet1").Cells(lngfindcells, 1) = "" Then
'Do Nothing
Else
'If cell on Sheet1 has a value, copy it and paste it into
Sheet2, beginning in column A, row 1
Worksheets("Sheet1").Range("A" & lngfindcells & ":A" &
lngfindcells).Copy Destination:=Worksheets("Sheet2").Range("A" &
lngdestcount)

'Increment counter so next value copied will be below
previous one on Sheet2
lngdestcount = lngdestcount + 1
End If
Next


--
kev_06
------------------------------------------------------------------------
kev_06's Profile: http://www.excelforum.com/member.php...o&userid=35046
View this thread: http://www.excelforum.com/showthread...hreadid=561602