Condensing a macro

[Guest]

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,ATT,PLAT,CMACPN,CUSTPN,TECH,CUSTACC,MAY06OOH,MAY06PRICE").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,ATT,PLAT,CMACPN,CUSTPN,TECH,CUSTACC,MAY06FORC,MAY06PRICE").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

 

[Guest]

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,MKTSEG,ATT,PLAT,CMACPN,CUSTPN,TECH,CUSTACC,MAY06OOH,MAY06PRICE")
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