copying cells from one worksheet to another

[Jerry]

I want to copy a range of cells from one worksheet to another worksheet. For
instance sheet1, (a1:a14) copy to sheet2 (a1:a14). If these cells are
already used insert the cells and downshift all other cells. I have a little
code but it overrides the cells and not insert them. This is the code that I
have tried to use.

Sub CreateLinkedSummary()
Dim SNames() As String
Dim myAdd As String
Dim myRange As Range
Dim mySS As Worksheet
Dim i As Integer
Dim SCnt As Integer
Dim myCol As Integer

SCnt = ActiveWindow.SelectedSheets.Count

If SCnt = 1 Then
If MsgBox("Are you sure - only one sheet?", vbYesNo) _
= vbYes Then
GoTo ShtOK
Else
MsgBox "Select the sheets and re-run the macro."
Exit Sub
End If
End If

ShtOK:

ReDim SNames(1 To SCnt)

For i = 1 To SCnt
SNames(i) = ActiveWindow.SelectedSheets(i).Name
Next i

Set myRange = Application.InputBox( _
"Select Range to link from", Type:=8)
myAdd = myRange.Address

Set myRange = Application.InputBox( _
"Select sheet and range to link to.", Type:=8)

Set mySS = myRange.Parent
myCol = myRange(1).Column
Worksheets(SNames(1)).Range(myAdd).Copy
mySS.Select
myRange.Select
mySS.Paste Link:=True

For i = 2 To SCnt
Worksheets(SNames(i)).Range(myAdd).Copy
mySS.Cells(mySS.Rows.Count, myCol).End(xlUp)(2).Select
mySS.Paste Link:=True
Next i

myRange.Select
Application.CutCopyMode = False
End Sub

what am i doing wrong?

 

[Andy Smith]

Sub InsertData()

If WorksheetFunction.CountA(Sheet2.Range("A1:A14")) > 0 Then
Sheet2.Range("A1:A14").Insert xlShiftDown
End If

Sheet1.Range("A1:A14").Copy Sheet2.Range("A1:A14")

End Sub