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.