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--
--
RyGuy
"Joel" wrote:
> 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:
>
> > 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---
> >
> >
> > --
> > RyGuy
|
Similar Threads
