Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
keeping original workbook open when saving
hello,
i work on a master workbook where a macro deletes rows from a worksheet. After deleteing the rows I want the resulting workbook to be saved with a specific filename. I am able to go this far but then the I want to be able to revert to the master workbook (prior to the rows being deleted) and run another macro for different rows to be deleted and save the new file with another filename. And then revert again ect. How can I acomplish this? Cause my macro results to the master workbook to be closed with the active worbook beng the saved worbook. Thank you, Steven. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
keeping original workbook open when saving
Can we see the code you're using?
Are we talking about a single workbook, or two workbooks? I presume a single workbook - that is, the macro is deleting rows in the 'master' workbook and saving the changed version of it under a different name? "steven" wrote: hello, i work on a master workbook where a macro deletes rows from a worksheet. After deleteing the rows I want the resulting workbook to be saved with a specific filename. I am able to go this far but then the I want to be able to revert to the master workbook (prior to the rows being deleted) and run another macro for different rows to be deleted and save the new file with another filename. And then revert again ect. How can I acomplish this? Cause my macro results to the master workbook to be closed with the active worbook beng the saved worbook. Thank you, Steven. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
keeping original workbook open when saving
Steven,
Generally: Copy the sheet or sheets to a new workbook, then do the processing, and use the SaveAs command, then close the newly saved book. For example: Sub MakeCopyEditAndSave() ThisWorkbook.Sheets("Sheet1").Copy 'Do processing here - call macro or put code ActiveWorkbook.SaveAs Filename:= _ "C:\Whatever\" & NameString & ".xls" _ , FileFormat:=xlNormal ActiveWorkbook.Close End Sub But if you have a specific logic for the row deletion, you might be able to automate the whole process..... HTH, Bernie MS Excel MVP "steven" wrote in message ... hello, i work on a master workbook where a macro deletes rows from a worksheet. After deleteing the rows I want the resulting workbook to be saved with a specific filename. I am able to go this far but then the I want to be able to revert to the master workbook (prior to the rows being deleted) and run another macro for different rows to be deleted and save the new file with another filename. And then revert again ect. How can I acomplish this? Cause my macro results to the master workbook to be closed with the active worbook beng the saved worbook. Thank you, Steven. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
keeping original workbook open when saving
This is the code I am using
Sub deleteifnot() lr = Cells(Rows.Count, "e").End(xlUp).Row For i = lr To 2 Step -1 If Cells(i, "e") < "70 - OPERATIONS" And _ Cells(i, "e") < "71 - " And _ Cells(i, "e") < "72 - " And _ Cells(i, "e") < "73 - " And _ Cells(i, "e") < "74 - MRP-PLANNING" Then Rows(i).Delete Next i ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "student" End Sub The above macro has to run again with different rows to be deleted. And yes I have a single (master) workbook and want to save the changed version of it under a different name or something to that effect. thank you. "JLatham" wrote: Can we see the code you're using? Are we talking about a single workbook, or two workbooks? I presume a single workbook - that is, the macro is deleting rows in the 'master' workbook and saving the changed version of it under a different name? "steven" wrote: hello, i work on a master workbook where a macro deletes rows from a worksheet. After deleteing the rows I want the resulting workbook to be saved with a specific filename. I am able to go this far but then the I want to be able to revert to the master workbook (prior to the rows being deleted) and run another macro for different rows to be deleted and save the new file with another filename. And then revert again ect. How can I acomplish this? Cause my macro results to the master workbook to be closed with the active worbook beng the saved worbook. Thank you, Steven. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
keeping original workbook open when saving
I think this is generally a good idea - saves having to save the original
book, save it with a new name, somehow restore back to the saved original without deleted rows, and go through those hoops each time deleting a new set of rows is required. I might even go so far as to suggest creating a new workbook via WorkBooks.Add and copying the sheet into the new book, doing the manipulation on the copy of the sheet in the new book, saving the new book under whatever name is required. I'll work up some example code later this evening - pressed for time right now. "Bernie Deitrick" wrote: Steven, Generally: Copy the sheet or sheets to a new workbook, then do the processing, and use the SaveAs command, then close the newly saved book. For example: Sub MakeCopyEditAndSave() ThisWorkbook.Sheets("Sheet1").Copy 'Do processing here - call macro or put code ActiveWorkbook.SaveAs Filename:= _ "C:\Whatever\" & NameString & ".xls" _ , FileFormat:=xlNormal ActiveWorkbook.Close End Sub But if you have a specific logic for the row deletion, you might be able to automate the whole process..... HTH, Bernie MS Excel MVP "steven" wrote in message ... hello, i work on a master workbook where a macro deletes rows from a worksheet. After deleteing the rows I want the resulting workbook to be saved with a specific filename. I am able to go this far but then the I want to be able to revert to the master workbook (prior to the rows being deleted) and run another macro for different rows to be deleted and save the new file with another filename. And then revert again ect. How can I acomplish this? Cause my macro results to the master workbook to be closed with the active worbook beng the saved worbook. Thank you, Steven. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
keeping original workbook open when saving
I might even go so far as to suggest creating a new workbook via
WorkBooks.Add and copying the sheet into the new book, doing the manipulation on the copy of the sheet in the new book, saving the new book under whatever name is required. That is exactly the method my macro uses: it copies the sheet directly into a new workbook, which is then manipulated and saved, then closed. Bernie |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
keeping original workbook open when saving
Then obviously there's no reason for me to reinvent that wheel is there? <g
"Bernie Deitrick" wrote: I might even go so far as to suggest creating a new workbook via WorkBooks.Add and copying the sheet into the new book, doing the manipulation on the copy of the sheet in the new book, saving the new book under whatever name is required. That is exactly the method my macro uses: it copies the sheet directly into a new workbook, which is then manipulated and saved, then closed. Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keeping program open even when closing workbook | Excel Discussion (Misc queries) | |||
Returning to Original Workbook After Saving Sheets | Excel Programming | |||
Do Until macro that renames workbooks and keeps the original workbook open | Excel Programming | |||
Keeping button pointing to original workbook | Excel Programming | |||
very difficult code that will close original workbook and leave another open | Excel Programming |