Paste Special, Values only

[EmmieLou]

I have part of the code here, I want to include a paste special to get only
the value of cell H3, not the formula. The value comes from a lookup
function in the sheet "Summary" and I want to paste the value in to another
worksheet, "Pedigrees."

Call InsertData("Pedigrees", "N", GetData("Summary", "H3"))
Thanks!!

 

[Barb Reinhardt]

You'll need to show the code that's in Sub InsertData. Showing the call
doesn't help much.

 

[EmmieLou]

Whoops, Sorry about that. Feels like Monday!

Function GetSurveyRow(ByVal PedigreeID As Integer) As Integer
Dim RowIndex As Integer
Dim MaxSurveyID As Integer
Dim CurrentSurveyID As String

MaxSurveyID = 0

For RowIndex = 2 To MAX_ROW
Sheets("Surveys").Select
Range("A" & RowIndex).Select
Range("A" & RowIndex).Select
CurrentSurveyID = ActiveCell.FormulaR1C1 & ""

If CurrentSurveyID = "" Then 'This is the end of the list =>
Insert new survey
Call InsertSurvey(MaxSurveyID + 1, PedigreeID)
GetSurveyRow = MaxSurveyID + 1
Exit For
Else
If CInt(CurrentSurveyID) > MaxSurveyID Then
MaxSurveyID = CInt(CurrentSurveyID)
End If
End If
Next
End Function

 

[EmmieLou]

Sub InsertPedigree(ByVal NewPedigreeID As Integer)
Call InsertData("Pedigrees", "A", NewPedigreeID)
Call InsertData("Pedigrees", "B", "Arizona CBM")
Call InsertData("Pedigrees", "C", "State")
Call InsertData("Pedigrees", "D", "BCS")
Call InsertData("Pedigrees", "E", "Year")
Call InsertData("Pedigrees", "F", "Plot")
Call InsertData("Pedigrees", "M", "Arizona")
Call InsertData("Pedigrees", "N", GetData("Summary", "H3"))
Call InsertData("Pedigrees", "O", GetData("Summary", "B3"))
Call InsertData("Pedigrees", "P", GetData("Summary", "A3"))
End Sub

 

[EmmieLou]

Sub InsertData(ByVal SheetName As String, ByVal Column As String, ByVal
Value As String)
Dim RowIndex As Integer

Sheets(SheetName).Select

'Find first empty row
For RowIndex = 2 To MAX_ROW
Range("A" & RowIndex).Select

If ActiveCell.FormulaR1C1 = "" Then
If Column <> "A" Then RowIndex = RowIndex - 1 'Go back one row
if we are not inserting data into the first field

Range(Column & RowIndex).Select

ActiveCell.FormulaR1C1 = Value
Exit For
End If
Next
End Sub

 

[EmmieLou]

Got It!!!, Thanks Though

 

[Barb Reinhardt]

I think I've cleaned up your code a bit. I like to avoid using SELECT if at
all possible because I find it slows down execution. Try it in a test
workbook if you want.

Sub InsertData(ByVal SheetName As String, ByVal Column As String, _
ByVal Value As String)
Dim RowIndex As Integer

Dim myWS As Worksheet
Dim myRange As Range

Set myWS = Nothing
On Error Resume Next
Set myWS = Sheets(SheetName)
On Error GoTo 0
If myWS Is Nothing Then
MsgBox ("There is no sheet of name '" & SheetName & "' in the
workbook")
Exit Sub
End If

Set myRange = myWS.Range(Column & "2")
If myRange.Column <> 1 Then
Set myRange = myRange.Offset(-1, 0)
End If

Do
Set myRange = myRange.Offset(1, 0)

Loop While Not IsEmpty(myRange)
myRange.Value = Value
End Sub