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>

 

[will1128]

I finally understood what you meant by the row and the UsedRange and did decide to use those values instead of the entire column. It makes more sense to only use the rows that are used instead of the all blank rows. I wasn't sure what the UsedRange was until I looked it up. Thank you for you help. I'm learning as I go.

 

[Ranaray]

Hi I am struggling with this code and want to paste format and formulas.

Sub Macro1()
' This macro takes values in the range myList
' and breaks it in to multiple lists
' and saves them to separate files.
Dim cell As Range
Dim curPath As String
curPath = ActiveWorkbook.Path & "\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each cell In Range("bizu")
[BU] = cell.Value
Range("myList1").AdvancedFilter Action:=xlFilterCopy, _
Criteriarange:=Range("Criteria"), copyToRange:=Range("bizextract"), unique:=False
Range(Range("bizExtract"), Range("bizExtract").End(xlDown)).Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.SaveAs Filename:=curPath & cell.Value & Format(Now, "dmmmyyyy-hhmmss") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range(Range("bizExtract"), Range("bizExtract").End(xlDown)).ClearContents
Next cell

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
please help