create a macro to copy a worksheet into another
 

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

create a macro to copy a worksheet into another
 

You can turn on the macro recorder (ToolsMacroRecord 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

create a macro to copy a worksheet into another
 

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 (ToolsMacroRecord 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

create a macro to copy a worksheet into another
 

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 (ToolsMacroRecord 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

create a macro to copy a worksheet into another
 

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 (ToolsMacroRecord 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

create a macro to copy a worksheet into another
 

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 (ToolsMacroRecord 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

create a macro to copy a worksheet into another
 

Debra,

This macro has out grown it's self and I AM AGAIN STRUGGLING!
Firstly I have columns up to BN but when I go and insert another column
between columns I get an error message - any ideas

also This macro which has been copied to create many reports from the data,
which works great, but I can't get it to copy some of the end columns. I
can't see any reason why. I will paste a copy of one macro below.

Sub MAB_Rollout_Schedule()

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,Q:S,V:W,Y:Z,AG:AH,AQ:AP,AW:BQ,B Y:BZ,CE:CZ").Delete
ActiveWorkbook.SaveAs Filename:= _
"C:\MAB_SCHEDULE FOLDER\MAB SCHEDULE.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Windows("PRP Rollout Schedule.xls").Activate
Range("A1:N5").Select
Selection.Copy
Windows("MAB SCHEDULE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Range("K2:K3").Select
Application.CutCopyMode = False
Selection.Cut Destination:=Range("F2:F3")
Range("J5").Select
ActiveWorkbook.Save
wbPRP.Activate
With wsCopy
.Activate
.Range("B6").Select
.Range("B6").AutoFilter
End With
Windows("MAB SCHEDULE.xls").Activate
ActiveWindow.DisplayGridlines = False
Range("B7:AF1927").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="Active"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A1").Select
ActiveWorkbook.Save
End Sub

If you have any idea please let me know. I am also going to post this as a
new question incase you are not available to help

Sean...







"Debra Dalgleish" wrote:

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 (ToolsMacroRecord 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

create a macro to copy a worksheet into another
 

If you give more detail on the error message, someone may be able to
help. If it says that it can't shift nonblank cells off the worksheet,
the information in the following MSKB article may help you:

Error message: To prevent possible loss of data, Microsoft Excel cannot
shift nonblank cells off the worksheet
http://support.microsoft.com/default.aspx?id=305568

If you select a cell in the table, and press Ctrl + * does it select the
entire table? If not, perhaps there are blank columns in the range, so
the right-hand columns aren't being copied in the macro.

Sean wrote:
Debra,

This macro has out grown it's self and I AM AGAIN STRUGGLING!
Firstly I have columns up to BN but when I go and insert another column
between columns I get an error message - any ideas

also This macro which has been copied to create many reports from the data,
which works great, but I can't get it to copy some of the end columns. I
can't see any reason why. I will paste a copy of one macro below.

Sub MAB_Rollout_Schedule()

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,Q:S,V:W,Y:Z,AG:AH,AQ:AP,AW:BQ,B Y:BZ,CE:CZ").Delete
ActiveWorkbook.SaveAs Filename:= _
"C:\MAB_SCHEDULE FOLDER\MAB SCHEDULE.xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
Windows("PRP Rollout Schedule.xls").Activate
Range("A1:N5").Select
Selection.Copy
Windows("MAB SCHEDULE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Range("K2:K3").Select
Application.CutCopyMode = False
Selection.Cut Destination:=Range("F2:F3")
Range("J5").Select
ActiveWorkbook.Save
wbPRP.Activate
With wsCopy
.Activate
.Range("B6").Select
.Range("B6").AutoFilter
End With
Windows("MAB SCHEDULE.xls").Activate
ActiveWindow.DisplayGridlines = False
Range("B7:AF1927").Select
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="Active"
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A1").Select
ActiveWorkbook.Save
End Sub

If you have any idea please let me know. I am also going to post this as a
new question incase you are not available to help

Sean...







"Debra Dalgleish" wrote:


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 (ToolsMacroRecord 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





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