active workbook/sheet

[larrysulky]

I have a VBA macro (WinXP, Excel 2003) whose job is to split out
individual rows from a worksheet and save the rows as independent
files, using the value in one of the cells to determine the file name.
Because I want to easily retain the layout (mainly the column widths),
the logic I'm using is like this:

1) Grab the header from row 1, plus the content of row 2.
2) Get the filename info out of row 2, columns C and D.
3) Clear all the rows from 3 onward.
4) Save the trimmed-down content as a new file, using the filename
calculated in step 2.
5) Re-open the original, main file.
6) Close the trimmed-down file.
7) Delete row 2 from the main file.

I'm setting this up to be run one line at a time, via a hot-key, so
that if problems arise they can be attended to immediately.

Everything works up through and including step 6. But I cannot get any
editing changes to "take" at step 7. In fact, I cannot even get
message boxes to pop up at that point. I suspect that I am somehow
failing to activate the proper workbook and/or proper worksheet (there
is only one worksheet in the main file).

Here is my code. You can see from commented-out lines that I've been
casting around in the dark, trying to find the lucky combination. I've
tried not activating, activating next, activating previous, activating
by sheet name and workbook name, etc. etc.

Any help would be appreciated. TIA--
--larry

Sub SaveByNameIsbn()
Dim thePath As String
Dim myMainWorkbook As Workbook
Dim myOutputWorkbook As Workbook
Dim theName As String
theName = ActiveWorkbook.FullName
Dim author As Range
Dim isbn As Range

Set author = Range("c2:c2").Item(1)
Set isbn = Range("D22").Item(1)

Dim myRows As Long
myRows = Rows.Count
Rows("3:" & myRows).Clear
'ActiveWorkbook.SaveAs fileName:=thePath & author & "_" & isbn
'set myOutputWorkbook =
Set myOutputWorkbook = ActiveWorkbook
'ActiveWorkbook.SaveAs fileName:=ActiveWorkbook.Path & "\" &
author & "_" & isbn
myOutputWorkbook.SaveAs fileName:=ActiveWorkbook.Path & "\" &
author & "_" & isbn

Set myMainWorkbook = Workbooks.Open(theName, , 0) ' Does zero
force file to be editable?
myOutputWorkbook.Close
'myMainWorkbook.Activate
myMainWorkbook.Sheets("Sheet1").Activate
'ActiveWindow.ActivatePrevious
'ActiveWorkbook.Close
'ActiveWindow.Close
'ActiveWindow.ActivateNext
'myMainWorkbook.Activate
'Dim myMainSheet As Worksheet
'Set myMainSheet = myMainWorkbook.Sheets(1)
'myMainWorkbook.Sheets(1).Activate
'myMainSheet.Activate
'myMainWorkbook.Sheets("Sheet1").Range("a2:a2").Select
'myMainWorkbook.Sheets("Sheet1").Rows("2:2").Select
MsgBox "debug" ' Please, at least SHOW UP
Rows("2:2").Select
Selection.Delete Shift:=xlUp

GoTo bye

bye:
End Sub

 

[JLGWhiz]

Switch steps 5 and 6.

I have a VBA macro (WinXP, Excel 2003) whose job is to split out
individual rows from a worksheet and save the rows as independent
files, using the value in one of the cells to determine the file name.
Because I want to easily retain the layout (mainly the column widths),
the logic I'm using is like this:

1) Grab the header from row 1, plus the content of row 2.
2) Get the filename info out of row 2, columns C and D.
3) Clear all the rows from 3 onward.
4) Save the trimmed-down content as a new file, using the filename
calculated in step 2.
5) Re-open the original, main file.
6) Close the trimmed-down file.
7) Delete row 2 from the main file.

I'm setting this up to be run one line at a time, via a hot-key, so
that if problems arise they can be attended to immediately.

Everything works up through and including step 6. But I cannot get any
editing changes to "take" at step 7. In fact, I cannot even get
message boxes to pop up at that point. I suspect that I am somehow
failing to activate the proper workbook and/or proper worksheet (there
is only one worksheet in the main file).

Here is my code. You can see from commented-out lines that I've been
casting around in the dark, trying to find the lucky combination. I've
tried not activating, activating next, activating previous, activating
by sheet name and workbook name, etc. etc.

Any help would be appreciated. TIA--
--larry

Sub SaveByNameIsbn()
Dim thePath As String
Dim myMainWorkbook As Workbook
Dim myOutputWorkbook As Workbook
Dim theName As String
theName = ActiveWorkbook.FullName
Dim author As Range
Dim isbn As Range

Set author = Range("c2:c2").Item(1)
Set isbn = Range("D22").Item(1)

Dim myRows As Long
myRows = Rows.Count
Rows("3:" & myRows).Clear
'ActiveWorkbook.SaveAs fileName:=thePath & author & "_" & isbn
'set myOutputWorkbook =
Set myOutputWorkbook = ActiveWorkbook
'ActiveWorkbook.SaveAs fileName:=ActiveWorkbook.Path & "\" &
author & "_" & isbn
myOutputWorkbook.SaveAs fileName:=ActiveWorkbook.Path & "\" &
author & "_" & isbn

Set myMainWorkbook = Workbooks.Open(theName, , 0) ' Does zero
force file to be editable?
myOutputWorkbook.Close
'myMainWorkbook.Activate
myMainWorkbook.Sheets("Sheet1").Activate
'ActiveWindow.ActivatePrevious
'ActiveWorkbook.Close
'ActiveWindow.Close
'ActiveWindow.ActivateNext
'myMainWorkbook.Activate
'Dim myMainSheet As Worksheet
'Set myMainSheet = myMainWorkbook.Sheets(1)
'myMainWorkbook.Sheets(1).Activate
'myMainSheet.Activate
'myMainWorkbook.Sheets("Sheet1").Range("a2:a2").Select
'myMainWorkbook.Sheets("Sheet1").Rows("2:2").Select
MsgBox "debug" ' Please, at least SHOW UP
Rows("2:2").Select
Selection.Delete Shift:=xlUp

GoTo bye

bye:
End Sub

 

[JLGWhiz]

I am not even sure that alone will cure the problem. But what was happening
was that you were closing the workbook that was actively running the code.
Since I didn't see the code, I assume you can use a macro to open the
original file and execute the delete.

The way you have it, the original workbook is opened in a second instance,
so when you execute the close of the save-as filename copy, it loses
communication with the second instance of the original.

 

[larrysulky]

Hmm...

Originally I did have steps 5 and 6 in the opposite order; I would
close the workbook (with its new name), then try to reopen the
original workbook. But since the macro was stored inside the workbook
itself, it stopped executing once the workbook was closed. I figured
(hoped) that by re-opening the original file...

Oh.

Now I get what you're saying. The instance of the macro that's running
stops when the first workbook is closed. The fact that the workbook
that gets reopened has identical code in it is irrelevant. That's not
the instance that is running. The instant I close the first workbook,
the macro processing stops.

I'll need to rethink this. I'd like an easy way to retain all the
column definitions; it seems that "save as" ought to be part of the
solution, rather than setting up an array to hold all the column
definitions (and I don't know how many columns there will be from one
spreadsheet to another.

Maybe I need to store the macro in a separate template file?

Thanks for your help, Whiz