Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Macro; Loop is Misbehaving
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Macro; Loop is Misbehaving
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/Paste Macro; Loop is Misbehaving
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: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy-paste started misbehaving (cell reference wrong) | Excel Discussion (Misc queries) | |||
copy paste loop---new to vba | Excel Programming | |||
Copy and Paste Loop | Excel Programming | |||
Copy/Paste Loop | Excel Programming | |||
copy and paste loop | Excel Programming |