Copy/Paste Macro; Loop is Misbehaving

[ryguy7272]

I’m 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. I’m 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---

 

[Joel]

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]

Awesome! I went with this:
Sub Macro1()

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

For Each c In .Range("AA1:AA11")

c.Copy
..Range("A3:A6").PasteSpecial Paste:=xlPasteValues
..Range("A11:A14").PasteSpecial Paste:=xlPasteValues

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 With
End Sub

I simply added an 'End With' and changed the structure of the loop a tad, so
that each variable in AA1:AA11 is copied/pasted into A3:A6 and A11:A14.
Adding the names to the sheets was a great idea. I've done it before; didn't
think of it this time, but when I saw your code, I knew that was definitely
the way to go!! Thanks so much!!

Regards,
Ryan--