Copy/Paste Macro; Loop is Misbehaving

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ryguy7272 - 29 May 2008 16:45 GMT

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---

Signature

RyGuy

Reply to this Message

Joel - 29 May 2008 17:26 GMT

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

> 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.
[quoted text clipped - 33 lines]
> Regards,
> Ryan---

Reply to this Message

ryguy7272 - 29 May 2008 17:44 GMT

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--

Signature

RyGuy

> 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
[quoted text clipped - 58 lines]
> > Regards,
> > Ryan---

Reply to this Message