create a macro to copy a worksheet into another

[Sean]

I am looking for some help!

I want to copy data from a work sheet called "House List" in a workbook
called "PRP Rollout Schedule"
The data is contained in B6:AX1728

There is a possiblity that there will be filters on so these will need to be
cleared first.
Copy the data into a new worksheet, but it is important that the data is
pasted at the same range as original B6:AX1728
then the following columns will need to be deleted:
f-i
p-r
y-ab
ad-af
ai-ak

can anyone help as this is beond me

[Debra Dalgleish]

You can turn on the macro recorder (Tools>Macro>Record Macro), and
record the steps as you manually create the new workbook.

To turn off AutoFilters, you can add this line to your recorded code:

'removes AutoFilter if one exists
Worksheets("House List").AutoFilterMode = False

Sean wrote:
> I am looking for some help!
>
> I want to copy data from a work sheet called "House List" in a workbook
> called "PRP Rollout Schedule"
> The data is contained in B6:AX1728
>
> There is a possiblity that there will be filters on so these will need to be
> cleared first.
> Copy the data into a new worksheet, but it is important that the data is
> pasted at the same range as original B6:AX1728
> then the following columns will need to be deleted:
> f-i
> p-r
> y-ab
> ad-af
> ai-ak
>
> can anyone help as this is beond me


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

[Sean]

Debra,

I have tried to record a macro so many times for this but it still keeps
doing it wrong.
I have tried hiding the columns in the original and copying to a new
workbook but I get all the columns show in the new workbook.
I have tried copying all and then deleting the columns in the new work book,
but then I get a column I dont want and a load of missing information that i
need.

here is a copy of the latest macro:
ActiveWorkbook.Save
Selection.AutoFilter
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
Columns("P:R").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("P:P").Select
Selection.Delete Shift:=xlToLeft
Columns("R:S").Select
Selection.Delete Shift:=xlToLeft
Columns("S:V").Select
Selection.Delete Shift:=xlToLeft
Columns("T:V").Select
Selection.Delete Shift:=xlToLeft
Columns("V:X").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Windows("PRP Rollout Schedule.xls").Activate
Range("B6:AX2072").Select
Selection.AutoFilter
Range("A1").Select
End Sub

put the save in at first (just in case!) undo filters, copy to new work
book, delete columns not needed, then go back to original and reinsert
filters.
Sound simple to me but won't work.

Sean...

"Debra Dalgleish" wrote:

> You can turn on the macro recorder (Tools>Macro>Record Macro), and
> record the steps as you manually create the new workbook.
>
> To turn off AutoFilters, you can add this line to your recorded code:
>
> 'removes AutoFilter if one exists
> Worksheets("House List").AutoFilterMode = False
>
> Sean wrote:
> > I am looking for some help!
> >
> > I want to copy data from a work sheet called "House List" in a workbook
> > called "PRP Rollout Schedule"
> > The data is contained in B6:AX1728
> >
> > There is a possiblity that there will be filters on so these will need to be
> > cleared first.
> > Copy the data into a new worksheet, but it is important that the data is
> > pasted at the same range as original B6:AX1728
> > then the following columns will need to be deleted:
> > f-i
> > p-r
> > y-ab
> > ad-af
> > ai-ak
> >
> > can anyone help as this is beond me
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

If you're deleting columns in groups, it's easier to keep track if you
work from right to left. So, delete AI:AK, then AD:AF, etc. Or, hold the
Ctrl key, and select all the columns, and delete them at the same time.

The following code may do what you want:

'=====================
Sub CopyToNewWkbk()

Dim wbPRP As Workbook
Dim wbNew As Workbook
Dim wsCopy As Worksheet
Dim wsPaste As Worksheet

Set wbPRP = Workbooks("PRP Rollout Schedule.xls")
Set wsCopy = wbPRP.Worksheets("House List")
Set wbNew = Workbooks.Add
Set wsPaste = wbNew.Worksheets(1)

wbPRP.Save
With wsCopy
.AutoFilterMode = False
.Range("B6").CurrentRegion.Copy _
Destination:=wsPaste.Range("B6")
End With

wsPaste.Range("F:I,P:R,Y:AB,AD:AF,AI:AK").Delete _
Shift:=xlToLeft
wbPRP.Activate
With wsCopy
.Activate
.Range("B6").Select
.Range("B6").AutoFilter
End With
End Sub
'======================

Sean wrote:
> Debra,
>
> I have tried to record a macro so many times for this but it still keeps
> doing it wrong.
> I have tried hiding the columns in the original and copying to a new
> workbook but I get all the columns show in the new workbook.
> I have tried copying all and then deleting the columns in the new work book,
> but then I get a column I dont want and a load of missing information that i
> need.
>
> here is a copy of the latest macro:
> ActiveWorkbook.Save
> Selection.AutoFilter
> Cells.Select
> Selection.Copy
> Workbooks.Add
> Cells.Select
> ActiveSheet.Paste
> Columns("P:R").Select
> Application.CutCopyMode = False
> Selection.Delete Shift:=xlToLeft
> Columns("P:P").Select
> Selection.Delete Shift:=xlToLeft
> Columns("R:S").Select
> Selection.Delete Shift:=xlToLeft
> Columns("S:V").Select
> Selection.Delete Shift:=xlToLeft
> Columns("T:V").Select
> Selection.Delete Shift:=xlToLeft
> Columns("V:X").Select
> Selection.Delete Shift:=xlToLeft
> Range("A1").Select
> Windows("PRP Rollout Schedule.xls").Activate
> Range("B6:AX2072").Select
> Selection.AutoFilter
> Range("A1").Select
> End Sub
>
> put the save in at first (just in case!) undo filters, copy to new work
> book, delete columns not needed, then go back to original and reinsert
> filters.
> Sound simple to me but won't work.
>
> Sean...
>
> "Debra Dalgleish" wrote:
>
>
>>You can turn on the macro recorder (Tools>Macro>Record Macro), and
>>record the steps as you manually create the new workbook.
>>
>>To turn off AutoFilters, you can add this line to your recorded code:
>>
>>'removes AutoFilter if one exists
>> Worksheets("House List").AutoFilterMode = False
>>
>>Sean wrote:
>>
>>>I am looking for some help!
>>>
>>>I want to copy data from a work sheet called "House List" in a workbook
>>>called "PRP Rollout Schedule"
>>>The data is contained in B6:AX1728
>>>
>>>There is a possiblity that there will be filters on so these will need to be
>>>cleared first.
>>>Copy the data into a new worksheet, but it is important that the data is
>>>pasted at the same range as original B6:AX1728
>>>then the following columns will need to be deleted:
>>>f-i
>>>p-r
>>>y-ab
>>>ad-af
>>>ai-ak
>>>
>>>can anyone help as this is beond me
>>
>>
>>--
>>Debra Dalgleish
>>Contextures
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

[Sean]

That's amazing, I really should learn how to do these properly. I can manage
in simple tasks, but this was out of my league. Many thanks much appriciated
Sean...

"Debra Dalgleish" wrote:

> If you're deleting columns in groups, it's easier to keep track if you
> work from right to left. So, delete AI:AK, then AD:AF, etc. Or, hold the
> Ctrl key, and select all the columns, and delete them at the same time.
>
> The following code may do what you want:
>
> '=====================
> Sub CopyToNewWkbk()
>
> Dim wbPRP As Workbook
> Dim wbNew As Workbook
> Dim wsCopy As Worksheet
> Dim wsPaste As Worksheet
>
> Set wbPRP = Workbooks("PRP Rollout Schedule.xls")
> Set wsCopy = wbPRP.Worksheets("House List")
> Set wbNew = Workbooks.Add
> Set wsPaste = wbNew.Worksheets(1)
>
> wbPRP.Save
> With wsCopy
> .AutoFilterMode = False
> .Range("B6").CurrentRegion.Copy _
> Destination:=wsPaste.Range("B6")
> End With
>
> wsPaste.Range("F:I,P:R,Y:AB,AD:AF,AI:AK").Delete _
> Shift:=xlToLeft
> wbPRP.Activate
> With wsCopy
> .Activate
> .Range("B6").Select
> .Range("B6").AutoFilter
> End With
> End Sub
> '======================
>
> Sean wrote:
> > Debra,
> >
> > I have tried to record a macro so many times for this but it still keeps
> > doing it wrong.
> > I have tried hiding the columns in the original and copying to a new
> > workbook but I get all the columns show in the new workbook.
> > I have tried copying all and then deleting the columns in the new work book,
> > but then I get a column I dont want and a load of missing information that i
> > need.
> >
> > here is a copy of the latest macro:
> > ActiveWorkbook.Save
> > Selection.AutoFilter
> > Cells.Select
> > Selection.Copy
> > Workbooks.Add
> > Cells.Select
> > ActiveSheet.Paste
> > Columns("P:R").Select
> > Application.CutCopyMode = False
> > Selection.Delete Shift:=xlToLeft
> > Columns("P:P").Select
> > Selection.Delete Shift:=xlToLeft
> > Columns("R:S").Select
> > Selection.Delete Shift:=xlToLeft
> > Columns("S:V").Select
> > Selection.Delete Shift:=xlToLeft
> > Columns("T:V").Select
> > Selection.Delete Shift:=xlToLeft
> > Columns("V:X").Select
> > Selection.Delete Shift:=xlToLeft
> > Range("A1").Select
> > Windows("PRP Rollout Schedule.xls").Activate
> > Range("B6:AX2072").Select
> > Selection.AutoFilter
> > Range("A1").Select
> > End Sub
> >
> > put the save in at first (just in case!) undo filters, copy to new work
> > book, delete columns not needed, then go back to original and reinsert
> > filters.
> > Sound simple to me but won't work.
> >
> > Sean...
> >
> > "Debra Dalgleish" wrote:
> >
> >
> >>You can turn on the macro recorder (Tools>Macro>Record Macro), and
> >>record the steps as you manually create the new workbook.
> >>
> >>To turn off AutoFilters, you can add this line to your recorded code:
> >>
> >>'removes AutoFilter if one exists
> >> Worksheets("House List").AutoFilterMode = False
> >>
> >>Sean wrote:
> >>
> >>>I am looking for some help!
> >>>
> >>>I want to copy data from a work sheet called "House List" in a workbook
> >>>called "PRP Rollout Schedule"
> >>>The data is contained in B6:AX1728
> >>>
> >>>There is a possiblity that there will be filters on so these will need to be
> >>>cleared first.
> >>>Copy the data into a new worksheet, but it is important that the data is
> >>>pasted at the same range as original B6:AX1728
> >>>then the following columns will need to be deleted:
> >>>f-i
> >>>p-r
> >>>y-ab
> >>>ad-af
> >>>ai-ak
> >>>
> >>>can anyone help as this is beond me
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Contextures
> >>http://www.contextures.com/tiptech.html
> >>
> >>
> >
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it helped.

Sean wrote:
> That's amazing, I really should learn how to do these properly. I can manage
> in simple tasks, but this was out of my league. Many thanks much appriciated
> Sean...
>
> "Debra Dalgleish" wrote:
>
>
>>If you're deleting columns in groups, it's easier to keep track if you
>>work from right to left. So, delete AI:AK, then AD:AF, etc. Or, hold the
>>Ctrl key, and select all the columns, and delete them at the same time.
>>
>>The following code may do what you want:
>>
>>'=====================
>>Sub CopyToNewWkbk()
>>
>>Dim wbPRP As Workbook
>>Dim wbNew As Workbook
>>Dim wsCopy As Worksheet
>>Dim wsPaste As Worksheet
>>
>>Set wbPRP = Workbooks("PRP Rollout Schedule.xls")
>>Set wsCopy = wbPRP.Worksheets("House List")
>>Set wbNew = Workbooks.Add
>>Set wsPaste = wbNew.Worksheets(1)
>>
>> wbPRP.Save
>> With wsCopy
>> .AutoFilterMode = False
>> .Range("B6").CurrentRegion.Copy _
>> Destination:=wsPaste.Range("B6")
>> End With
>>
>> wsPaste.Range("F:I,P:R,Y:AB,AD:AF,AI:AK").Delete _
>> Shift:=xlToLeft
>> wbPRP.Activate
>> With wsCopy
>> .Activate
>> .Range("B6").Select
>> .Range("B6").AutoFilter
>> End With
>>End Sub
>>'======================
>>
>>Sean wrote:
>>
>>>Debra,
>>>
>>>I have tried to record a macro so many times for this but it still keeps
>>>doing it wrong.
>>>I have tried hiding the columns in the original and copying to a new
>>>workbook but I get all the columns show in the new workbook.
>>>I have tried copying all and then deleting the columns in the new work book,
>>>but then I get a column I dont want and a load of missing information that i
>>>need.
>>>
>>>here is a copy of the latest macro:
>>>ActiveWorkbook.Save
>>> Selection.AutoFilter
>>> Cells.Select
>>> Selection.Copy
>>> Workbooks.Add
>>> Cells.Select
>>> ActiveSheet.Paste
>>> Columns("P:R").Select
>>> Application.CutCopyMode = False
>>> Selection.Delete Shift:=xlToLeft
>>> Columns("P:P").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Columns("R:S").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Columns("S:V").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Columns("T:V").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Columns("V:X").Select
>>> Selection.Delete Shift:=xlToLeft
>>> Range("A1").Select
>>> Windows("PRP Rollout Schedule.xls").Activate
>>> Range("B6:AX2072").Select
>>> Selection.AutoFilter
>>> Range("A1").Select
>>>End Sub
>>>
>>>put the save in at first (just in case!) undo filters, copy to new work
>>>book, delete columns not needed, then go back to original and reinsert
>>>filters.
>>>Sound simple to me but won't work.
>>>
>>>Sean...
>>>
>>>"Debra Dalgleish" wrote:
>>>
>>>
>>>
>>>>You can turn on the macro recorder (Tools>Macro>Record Macro), and
>>>>record the steps as you manually create the new workbook.
>>>>
>>>>To turn off AutoFilters, you can add this line to your recorded code:
>>>>
>>>>'removes AutoFilter if one exists
>>>> Worksheets("House List").AutoFilterMode = False
>>>>
>>>>Sean wrote:
>>>>
>>>>
>>>>>I am looking for some help!
>>>>>
>>>>>I want to copy data from a work sheet called "House List" in a workbook
>>>>>called "PRP Rollout Schedule"
>>>>>The data is contained in B6:AX1728
>>>>>
>>>>>There is a possiblity that there will be filters on so these will need to be
>>>>>cleared first.
>>>>>Copy the data into a new worksheet, but it is important that the data is
>>>>>pasted at the same range as original B6:AX1728
>>>>>then the following columns will need to be deleted:
>>>>>f-i
>>>>>p-r
>>>>>y-ab
>>>>>ad-af
>>>>>ai-ak
>>>>>
>>>>>can anyone help as this is beond me
>>>>
>>>>
>>>>--
>>>>Debra Dalgleish
>>>>Contextures
>>>>http://www.contextures.com/tiptech.html
>>>>
>>>>
>>>
>>
>>--
>>Debra Dalgleish
>>Contextures
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html