Try soemthing like this. Not sure if you meant column A or column AA.
Taking AA1:AA11 are 11 values. Pasting into A3 and A11 you will loose some
values because the two ranges overlap. When pasting data you only need the
1st cell location not the entire range.

Sub Macro1()

With Sheets("Report")
lstRw = .Cells(Rows.Count, "AA").End(xlUp).Row

.Range("AA1:AA11").Copy
.Range("A3").PasteSpecial Paste:=xlPasteValues
.Range("A11").PasteSpecial Paste:=xlPasteValues
For Each c In .Range("AA1:AA11")


Set newsht = Sheets.Add(after:=Sheets(Sheets.Count))

.Range("A1:O17").Copy _
Destination:=newsht.Range("A1")
newsht.Cells.Columns.AutoFit
newsht.Name = c
Next c
End Sub

"ryguy7272" wrote:

Im trying to come up with a macro that takes names in a list, Range is
AA1:AA11, copy paste each name into both Range A3:A6 and Range A11:A14.
Several links, all using GetPivotTable functions, will update with the name
in those ranges. Then I want to copy/paste the Range A1:O17 to a new Sheet
and loop to the next name. Im sure it is possible. I am not sure of how to
do it. Can someone please assist?

I think it is going to look something like this:
Sub Macro1()

For Each c In Sheets("Report").Range("A1:A11")
lstRw = Cells(Rows.Count, 27).End(xlUp).Row

ActiveCell.Select
Selection.Copy


Range("A3:A6,A11:A14").Select
Range("A11").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1:O17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Selection.Columns.AutoFit
Range("A1").Select

Next c
End Sub

It keeps copying/pasting the value in A1, in Sheet "Report" to
Range("A3:A6,A11:A14").Select.


Regards,
Ryan---


--
RyGuy