way to Copy and Paste

[RigasMinho]

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.

[Jim Jackson]

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

[kev_06]

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

[RigasMinho]

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