I am baffled with this. I've never seen vba code pasted in the actual excel spreadsheet before. Here's the code I'm calling:
Sub GetInputAndCopyData(strSheetName As String, strColName As String)
Dim rngInp As Range
If IsValidCell(strSheetName, strColName) Then
Set rngInp = ActiveWorkbook.Worksheets(strSheetName).Range(strColName & "6")
Set rngInp = Range(rngInp, rngInp.End(xlDown))
rngInp.copy Destination:=Selection
End If
End Sub
' check that cell is valid
' this first checks that the sheet name is valid, then checks that cell
' has data in rows 1 and 2
Function IsValidCell(ByVal wks As String, ByVal cell As String) As Boolean
Dim r As Range
On Error GoTo NotValid
If IsValidSheet(wks) Then
' error if column does not exist
Set r = ThisWorkbook.Worksheets(wks).Range(cell)
' check that column has data, must have data in at least first two rows
If r.Value = "" And r.Offset(1, 0).Value = "" Then
IsValidColumn = False
Else
IsValidColumn = True
End If
End If
Exit Function
NotValid:
IsValidColumn = False
End Function
Function IsValidSheet(ByVal str As String) As Boolean
Dim wks As Worksheet
On Error GoTo NotValid
' error if sheet does not exist
Set wks = ThisWorkbook.Worksheets(str)
IsValidSheet = True
Exit Function
NotValid:
IsValidSheet = False
End Function
Here's how I'm calling it:
Call GetInputAndCopyData("Analog", "EMS Composite Name")
Sheets("Analog").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Here's what is returned and pasted in the spreadsheet:
Sheets("Analog").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Any suggestions on how to copy and paste the range instead of the actual code would be great.
Bookmarks