copying cells from one worksheet to several others

[Jerry]

I have a worksheet that I want to copy cells a1:a14 unto other several
worksheets. I want these cells to be selected and then inserted into the
other worksheets to the same range. Can not copy them because it replaces
the info and right now I can only select once and then insert special. I
have to copy this info to about 70 other worksheets and I want to automate
it. Can anyone assist me with this task? Thanks in advance.

 

[Patrick Molloy]

I don't understand by what you mean when you say you can't copy but can only
insert special.

 

[Jerry]

If I use copy it doesn't shift the existing data down that number of rows, it
just copies on top of that data, I need to insert that number of blank rows
then paste or insert special that same number of rows/cols being copied.

 

[Patrick Molloy]

this is an idea .. let me know what refinements you want -this is pretty basic

Option Explicit

Sub CopyInsertData()
Dim ws As Worksheet
Const sAddress As String = "A1:A14"
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then

ws.Range(sAddress).insert xlShiftToRight
Worksheets("Sheet1").Range(sAddress).Copy
ws.Range(sAddress).PasteSpecial xlPasteAll
End If
Next
End Sub