| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet 1 exact copy of worksheet 2, but in different order | Excel Discussion (Misc queries) | |||
| Getting macro to copy to starting cell | Excel Worksheet Functions | |||
| macro to copy and edit then delete a worksheet | Excel Discussion (Misc queries) | |||
| How to copy subtotalled cells to a new worksheet (in a macro), wi. | Excel Discussion (Misc queries) | |||
| Macro to search for and display data in another worksheet | Excel Worksheet Functions | |||
Hybrid Mode
