Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing a macro
The macro below copies some named ranges, pastes them into a new sheet and
then goes back copies 10 of the same ranges plus two new ones.This will have to repeat its self 32 times to get the data the way I want it (make a database from a table)! So if there is a way of condensing this macro or make it more efficent I would be very grateful. Thanks, Esther With Worksheets("Master").Range("CUST,REG,RSM,MKTSEG,AT T,PLAT,CMACPN,CUSTPN,TECH,CUSTACC,MAY06OOH,MAY06PR ICE").Copy Sheets("New").Select Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'NEXT Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Worksheets("Master").Range("CUST,REG,RSM,MKTSEG,AT T,PLAT,CMACPN,CUSTPN,TECH,CUSTACC,MAY06FORC,MAY06P RICE").Copy Sheets("New").Select ActiveCell.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'NEXT Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing a macro
Hi
you could use a sub similar to: '---------------------------------------------- 'Copy origin range (RgO) and paste in last cell in ' destination (rgD) column Sub AddDataToSheet(RgO As Range, RgD As Range) Dim rg As Range If RgO Is Nothing Or RgD Is Nothing Then Exit Sub Set rg = RgD.EntireColumn Set rg = rg.Cells(rg.Cells.Count).End(xlUp).Offset(1, 0) RgO.Copy RgD.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False End Sub '--------------------------------------------------------- now call it using: Sub test() dim rg1 as range, rg2 as range set rg1=Worksheets("Master").Range("CUST,REG,RSM,MKTSE G,ATT,PLAT,CMACPN,CUSTPN,TECH,CUSTACC,MAY06OOH,MAY 06PRICE") set rg2=workSheets("New").range("A2") 'either call the sub each time or put in a loop. AddDataToSheet rg1,rg2 AddDataToSheet rg1,rg2 AddDataToSheet rg1,rg2 end sub -- Regards, Sébastien <http://www.ondemandanalysis.com "EstherJ" wrote: The macro below copies some named ranges, pastes them into a new sheet and then goes back copies 10 of the same ranges plus two new ones.This will have to repeat its self 32 times to get the data the way I want it (make a database from a table)! So if there is a way of condensing this macro or make it more efficent I would be very grateful. Thanks, Esther With Worksheets("Master").Range("CUST,REG,RSM,MKTSEG,AT T,PLAT,CMACPN,CUSTPN,TECH,CUSTACC,MAY06OOH,MAY06PR ICE").Copy Sheets("New").Select Range("A2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'NEXT Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select Worksheets("Master").Range("CUST,REG,RSM,MKTSEG,AT T,PLAT,CMACPN,CUSTPN,TECH,CUSTACC,MAY06FORC,MAY06P RICE").Copy Sheets("New").Select ActiveCell.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'NEXT Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Select End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condensing VBA code | Excel Discussion (Misc queries) | |||
Condensing Columns | New Users to Excel | |||
Condensing data from 3D to 2D | Excel Worksheet Functions | |||
Condensing my worksheet | Excel Worksheet Functions | |||
Condensing lists | Excel Worksheet Functions |