copying selected cells and paste into new worksheet

[justin319]

Hi guys, I'm trying to learn macro programming for Excel as well. can you give me tips on how to select and copy only desired cells in a given column? aside from the record macro function is it possible for me to make the code more general?

does a loop and if a good start or there is a much shorter way? thanks


J

 

[SkipVought]

Hi,

What is the criteria for "copying" a cell.

FYI, there's a difference between COPYING a cell, which includes ALL the properties associated with it and referencing the VALUE property of the cell.

Skip,
[sub]
[red][/red]
[/sub]

 

[justin319]

hmmmm.. lets say we have column A. in this column, id only select all cells that has a value of 3 digit numbers. i'd copy them all and paste them on a new worksheet


i hope its clear thanks

J

 

[SkipVought]

What kind of data is in this column?

Are thre alpha characters also?

If you're looking for 3-digit numbers, these are values between 100 and 999. You could use the AutoFilter. Then Copy the results and Paste Special VALUES.

Skip,
[sub]
[red][/red]
[/sub]

 

[christhedonstar]

You can make this more generic as it assumes the data you start with one column and that you've defined named ranges with the data you want copy from. Its also quite long code to do a simple thing... But that might be good for you as there are different types of objects and things for you (but not necessarily for the solution) to play with. You should pick up a few things from the comments

Sub copyPasteOnCriteria()
'Store the results in a list
Dim resultList As Collection
Dim dataRange As Range
Dim cellInRange As Range

Dim resultVariant As Variant
Dim resultRange As Range

'set our objects to point to an object.
Set resultRange = Range("results")
Set dataRange = Range("myData")
Set resultList = New Collection

'To hold the cell value so we don't keep on checking our cell value using range.value
Dim cellValue As Variant

'Loop through every cell in our range of cells.
For Each cellInRange In dataRange.Cells
cellValue = cellInRange.value

'Check if the cell has number in it
If Application.WorksheetFunction.IsNumber(cellValue) Then
If cellValue > 99 Or cellValue < 1000 Then
' Lets add the number to our list
resultList.Add cellValue
End If
End If


Next cellInRange

Dim counter As Integer: counter = 1

'Now copy the results to the result range
For Each cellValue In resultList

resultRange.Cells(counter).value = cellValue
counter = counter + 1

Next cellValue

End Sub

Hope that helps...

 

[christhedonstar]

Here is another example, but you have to know what you mean what you mean by 3 digit numbers...

Sub copyPasteOnCriteria2()

Dim dataRangeAnchor As Range
Dim destinationAnchor As Range

Set dataRangeAnchor = Range("dataAnchor")
Set destinationAnchor = Range("resultAnchor")

Dim dataCounter As Long
Dim destinationCounter As Long

Dim cellValue As Variant


Do While True
cellValue = dataRangeAnchor.Offset(dataCounter).Value
If cellValue = "" Then Exit Do 'If we have an empty cell we assume that there is no other data in our set of data.
If Application.WorksheetFunction.IsNumber(cellValue) Then
' check the cell value
If cellValue >= 100 Or cellValue <= 999 Then 'If Len(cellValue) = 3 Then
'Add to our other range

destinationAnchor.Offset(destinationCounter).Value = cellValue
destinationCounter = destinationCounter + 1
End If

End If

dataCounter = dataCounter + 1

Loop



End Sub