Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Copy; Paste; Paste Special are disabled | Excel Discussion (Misc queries) | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |