help with copying value and not formula

[will1128]

I'm trying to copy the value of a formula and not the formula from one worksheet to another if and only if the cell is found. I've been able to copy the the column, but I get a #REF error on the 2nd worksheet.

Here's the code:
<code>
Public Function copy_col_to_new_sheet(Col As String) As Range
'Dim Col As String
Dim FoundCell As Range
FindString = Col 'value sent in from the function

If Trim(FindString) <> "" Then
With ActiveWorkbook.Sheets(1).Range("A1:IV65536")
'With ThisWorkbook.Sheets(1).Range("A1:IV65536")
Set FoundCell = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
'if my cell is found copy to 2nd worksheet
If Not FoundCell Is Nothing Then
Application.Goto FoundCell, True
MsgBox FoundCell.Address & FoundCell.Text & " " & "Found in Copy_Col" 'returns cell address
'MsgBox FoundCell.Columns & " " & "Columns" 'returns the column number as a Long
FoundCell.Cells.EntireColumn.Copy ActiveWorkbook.Sheets(2).Columns(FoundCell.Column) 'this works
Else
MsgBox "Nothing found Copy Col To New Sheet"
End If
End With
End If
End Function
<code>

 

[lenze]

A lot of code for such a simple task!!
Will the value occur only once in Sheet1?
If found, you want to copy all values in That column to Sheet2?
Including Row 1??

lenze

 

[will1128]

The value may or may not occur in Sheet 1, hence the find and all the code. Really inexperienced at this, obviously.

I want to copy all the values of the column including the foundCell.

 

[lenze]

So

Sub SearchMe()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LR As Long
Dim C As Range
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
With ws1.UsedRange
    Set C = .Find(InputBox("Enter Value to Find"), LookIn:=xlValues)
    If Not C Is Nothing Then
    LR = ws1.Cells(Rows.Count, C.Column).End(xlUp).Row
    ws1.Cells(1, C.Column).Resize(LR, 1).Copy
    ws2.Cells(1, C.Column).PasteSpecial xlPasteValues
    Else: MsgBox "Value not found"
    End If
End With
End Sub

lenze

 

[will1128]

In this case a user is not involved and using an input box is not what I need. The data on Sheet 1 is already provided and needs to be manipulated and placed on sheet 2. Part of the reason that code is so complex is an array filters through various strings. I'll keep reviewing and see if it helps.

 

[will1128]

.... thank you...figured it out with your help

<code>
If Trim(FindString) <> "" Then
With wksht1.Range("A1:IV65536")
'With ThisWorkbook.Sheets(1).Range("A1:IV65536")
Set FoundCell = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
'if my cell is found copy to 2nd worksheet
If Not FoundCell Is Nothing Then
Application.Goto FoundCell, True
MsgBox FoundCell.Address & FoundCell.Text & " " & "Found in Copy_Col" 'returns cell address
'MsgBox FoundCell.Columns & " " & "Columns" 'returns the column number as a Long
'FoundCell.Cells.EntireColumn.Copy ActiveWorkbook.Sheets(2).Columns(FoundCell.Column) 'this works
wksht1.Cells(1, FoundCell.Column).EntireColumn.Copy
wksht2.Cells(1, FoundCell.Column).PasteSpecial xlPasteValues
</code>

 

[lenze]

If you don't have userinterface, then use whatever you want in the Find Method. The principle of Finding and Copying will not change!!

lenze

 

[lenze]

I'm glad it works, but you still have several things that will slow your code down. Probably not an issue, as I suspect it still executes in about 1 second!!

lenze

 

[will1128]

Is there a way to keep the values format...

I.e a number is a number, a date a date... something like

xlFormat = true?

 

[lenze]

Why copy the Entire Column instead of the Used Rows. See my example
And why look in the entire sheet instead of just the UsedRange. Your current code might have problems if used on Excel 2007.

But, for Formats and Values, change

wksht2.Cells(1, FoundCell.Column).PasteSpecial xlPasteValues

to

wksht2.Cells(1, FoundCell.Column).PasteSpecial xlPasteValuesAndNumberFormats

lenze