I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

I was just about to step into a meeting for an hour or so. Hopefully someone
around here will be a ble to help you in the mean time...
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

If you select all of the cells in the target workbook and then paste them in
your master you will overwrite the sheet each time. Is that what you want to
do??? Additionally the code I posted assumed you were not closing the target
workbooks. You will want something more like this...

sub test
dim wbkTarget as workbook
dim lng as long

for lng = 1 to workbooks.count
set wbktarget = workbooks(lng)
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next lng
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

Jim,

Again, thank you so much for your response. In answer to your question, no,
I will not be copying/pasting into the same sheet tab each time. I still
have code to write that determines the correct sheet tab for the data to be
pasted into within the Master file--it will be based upon a value contained
in cell A10 within each "Book". I figured I could get to that part after I
figured out the basic copy/paste routine!! Anyway, I tried your code and it
is VERY CLOSE to working. The one problem I had is that it seems to "skip"
every other file. For example, when I ran my test, I had Books 1-10 open.
It copied all of the odd numbered files (1,3,5,7,9) but skipped the even. Is
there a minor tweak I need to make?

"Jim Thomlinson" wrote:

If you select all of the cells in the target workbook and then paste them in
your master you will overwrite the sheet each time. Is that what you want to
do??? Additionally the code I posted assumed you were not closing the target
workbooks. You will want something more like this...

sub test
dim wbkTarget as workbook
dim lng as long

for lng = 1 to workbooks.count
set wbktarget = workbooks(lng)
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next lng
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

Jim,

In answer to your question, no, I won't be copying/pasting into the same
sheet tab within the master file each time. I still have code to write that
will determine the correct sheet tab for pasting---it will be based upon the
value in cell A10 of each "Book". I thought I'd tackle that piece after
nailing down the basic copy/paste routine!! Anyway, the revised code you
sent me is VERY CLOSE to working. The only issue is that it seems to be
"skipping" every other workbook. For example, in the test I ran, I had Books
1-10 open. The macro copied books 1,3,5,7,9 -- but it did NOT copy 2,4,6,8.
Any suggestions? Again, thank you so much for your help.

"Jim Thomlinson" wrote:

If you select all of the cells in the target workbook and then paste them in
your master you will overwrite the sheet each time. Is that what you want to
do??? Additionally the code I posted assumed you were not closing the target
workbooks. You will want something more like this...

sub test
dim wbkTarget as workbook
dim lng as long

for lng = 1 to workbooks.count
set wbktarget = workbooks(lng)
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next lng
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

Sorry the code gets messed up by closing the books... Try this...

sub test
dim wbkTarget as workbook

for each wbktarget in workbooks
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
.wbktarget.close SaveChanges:=false
end if
end with
next wbktarget
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Jim,

In answer to your question, no, I won't be copying/pasting into the same
sheet tab within the master file each time. I still have code to write that
will determine the correct sheet tab for pasting---it will be based upon the
value in cell A10 of each "Book". I thought I'd tackle that piece after
nailing down the basic copy/paste routine!! Anyway, the revised code you
sent me is VERY CLOSE to working. The only issue is that it seems to be
"skipping" every other workbook. For example, in the test I ran, I had Books
1-10 open. The macro copied books 1,3,5,7,9 -- but it did NOT copy 2,4,6,8.
Any suggestions? Again, thank you so much for your help.

"Jim Thomlinson" wrote:

If you select all of the cells in the target workbook and then paste them in
your master you will overwrite the sheet each time. Is that what you want to
do??? Additionally the code I posted assumed you were not closing the target
workbooks. You will want something more like this...

sub test
dim wbkTarget as workbook
dim lng as long

for lng = 1 to workbooks.count
set wbktarget = workbooks(lng)
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next lng
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

Ok, tried the new code & when I tried to execute, got an 'Object Required'
error message on this line of code

..wbktarget.close SaveChanges:=false

"Jim Thomlinson" wrote:

Sorry the code gets messed up by closing the books... Try this...

sub test
dim wbkTarget as workbook

for each wbktarget in workbooks
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
.wbktarget.close SaveChanges:=false
end if
end with
next wbktarget
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Jim,

In answer to your question, no, I won't be copying/pasting into the same
sheet tab within the master file each time. I still have code to write that
will determine the correct sheet tab for pasting---it will be based upon the
value in cell A10 of each "Book". I thought I'd tackle that piece after
nailing down the basic copy/paste routine!! Anyway, the revised code you
sent me is VERY CLOSE to working. The only issue is that it seems to be
"skipping" every other workbook. For example, in the test I ran, I had Books
1-10 open. The macro copied books 1,3,5,7,9 -- but it did NOT copy 2,4,6,8.
Any suggestions? Again, thank you so much for your help.

"Jim Thomlinson" wrote:

If you select all of the cells in the target workbook and then paste them in
your master you will overwrite the sheet each time. Is that what you want to
do??? Additionally the code I posted assumed you were not closing the target
workbooks. You will want something more like this...

sub test
dim wbkTarget as workbook
dim lng as long

for lng = 1 to workbooks.count
set wbktarget = workbooks(lng)
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next lng
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

Try this... I goofed up...

sub test
dim wbkTarget as workbook

for each wbktarget in workbooks
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next wbktarget
end sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Sorry the code gets messed up by closing the books... Try this...

sub test
dim wbkTarget as workbook

for each wbktarget in workbooks
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
.wbktarget.close SaveChanges:=false
end if
end with
next wbktarget
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Jim,

In answer to your question, no, I won't be copying/pasting into the same
sheet tab within the master file each time. I still have code to write that
will determine the correct sheet tab for pasting---it will be based upon the
value in cell A10 of each "Book". I thought I'd tackle that piece after
nailing down the basic copy/paste routine!! Anyway, the revised code you
sent me is VERY CLOSE to working. The only issue is that it seems to be
"skipping" every other workbook. For example, in the test I ran, I had Books
1-10 open. The macro copied books 1,3,5,7,9 -- but it did NOT copy 2,4,6,8.
Any suggestions? Again, thank you so much for your help.

"Jim Thomlinson" wrote:

If you select all of the cells in the target workbook and then paste them in
your master you will overwrite the sheet each time. Is that what you want to
do??? Additionally the code I posted assumed you were not closing the target
workbooks. You will want something more like this...

sub test
dim wbkTarget as workbook
dim lng as long

for lng = 1 to workbooks.count
set wbktarget = workbooks(lng)
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next lng
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?

That's the ticket!! Thank you SO much for all your help!!

"Jim Thomlinson" wrote:

Try this... I goofed up...

sub test
dim wbkTarget as workbook

for each wbktarget in workbooks
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next wbktarget
end sub

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Sorry the code gets messed up by closing the books... Try this...

sub test
dim wbkTarget as workbook

for each wbktarget in workbooks
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
.wbktarget.close SaveChanges:=false
end if
end with
next wbktarget
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Jim,

In answer to your question, no, I won't be copying/pasting into the same
sheet tab within the master file each time. I still have code to write that
will determine the correct sheet tab for pasting---it will be based upon the
value in cell A10 of each "Book". I thought I'd tackle that piece after
nailing down the basic copy/paste routine!! Anyway, the revised code you
sent me is VERY CLOSE to working. The only issue is that it seems to be
"skipping" every other workbook. For example, in the test I ran, I had Books
1-10 open. The macro copied books 1,3,5,7,9 -- but it did NOT copy 2,4,6,8.
Any suggestions? Again, thank you so much for your help.

"Jim Thomlinson" wrote:

If you select all of the cells in the target workbook and then paste them in
your master you will overwrite the sheet each time. Is that what you want to
do??? Additionally the code I posted assumed you were not closing the target
workbooks. You will want something more like this...

sub test
dim wbkTarget as workbook
dim lng as long

for lng = 1 to workbooks.count
set wbktarget = workbooks(lng)
with wbktarget
if left(.name, 4) = "Book" then
.sheets("Sheet1").cells.copy thisworkbook.sheets("Dollars").range("A1")
.close SaveChanges:=False
end if
end with
next lng
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

Thank you for your quick response. I am still having issues. I tried using
your suggestion, but it didn't seem to work and I'm sure it's because I have
something wrong. FYI, the code is executed from within the Master file. It
is supposed to go to "Book1", highlight all cells, then go back to the Master
file & paste the data into a specific sheet tab. It should then return to
"Book1", close that workbook, then proceed to "Book2", etc. until it
completes all files that have "Book" as the prefix.

Sub test()

Dim CAGtemplate
Sheets("Xref").Range("A1").Select
CAGtemplate = ActiveCell.Value

Dim wbk As Workbook

For Each wbk In Workbooks
If Left(wbk.Name, 4) = "Book" Then
Cells.Select
Selection.Copy
Windows(CAGtemplate).Activate
Sheets("Dollars").Select
Range("A1").Select
ActiveSheet.Paste
Windows(wbk).Activate
ActiveWindow.Close
Else
End If

Next wbk
End Sub


"Jim Thomlinson" wrote:

Two possibilities. On is to validate the file name. the other is to validate
the contents of the workbook. If all of your files strictly follow a naming
conventions then:

sub test()
dim wbk as workbook

for each wbk in workbooks
if left(wbk.name, 6) = "MyFile" then 'or whtever the naming convention
msgbox wbk.name
end if
next wbk
end sub

If the books have not been save or do not follow a stric naming convention
then you need to look for som identifying feature in the workbook. This could
be a very hidden sheet that you add at the time the workbooks are created or
some other bit of info that uniquely identifies the file type.
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I should have added this as well --- how do I ensure this code is only
executed against the files that contain the naming convention of Book1,
Book2, etc. In other words, I do not want the code to execute against the
Master file, and I also don't want it to execute against anything like a
Personal.xls file or other type of background file that may open
automatically when the user opens their Excel application.

"Jim Thomlinson" wrote:

Try code something like this...

sub test()
dim wbk as workbook

for each wbk in workbooks
msgbox wbk.name
next wbk
end sub
--
HTH...

Jim Thomlinson


"JDaywalt" wrote:

I have a 'Master' file that contains code in which I need to access other
open files and perform a subset of code against each of them. The possible
number of open files is anywhere from 1 to 12---and each of the files will be
named sequentially Book1, Book2, Book3, etc. as they will have been generated
from a previous 'Copy Sheet' process performed by the user. How can I write
my code such that it identifies the number of these "Books" that are open,
then loops through each of them (performing the addt'l code), then stopping
when it reaches the last open book?