Open file and print sheets based on data in cell
 

Hi everyone. In column A I have the full path of a file. In columns B
through AZ I have sheet names within that file. How can I code excel to
open the file in A1, print the sheets in B1 through AZ1, then close the
file, then move to the next row and do the same until all rows containing
data in column A have been looped though? Thank you in advance.

PS - Some files may only have 2 sheets to be printed, so D1 through AZ1 may
be blank.

Open file and print sheets based on data in cell
 

Dim rng as Range, rng1 as Range
Dim cell as Range, cell1 as Range
Dim bk as Workbook
set rng = Range("A1").CurrentRegion.columns(1)
for each cell in rng
if trim(cell.value) < "" then
set bk = Workbooks.Open(cell.Value)
set rng1 = Cell.offset(0,1).Resize(1,51).SpecialCells(xlConst ants)
for each cell1 in rng1
if trim(cell1.Value) < "" then
bk.worksheets(cell1.Value).Printout
end if
Next
End if
Next

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi everyone. In column A I have the full path of a file. In columns B
through AZ I have sheet names within that file. How can I code excel to
open the file in A1, print the sheets in B1 through AZ1, then close the
file, then move to the next row and do the same until all rows containing
data in column A have been looped though? Thank you in advance.

PS - Some files may only have 2 sheets to be printed, so D1 through AZ1
may
be blank.

Open file and print sheets based on data in cell
 

Thanks Tom!

"Tom Ogilvy" wrote in message
...
Dim rng as Range, rng1 as Range
Dim cell as Range, cell1 as Range
Dim bk as Workbook
set rng = Range("A1").CurrentRegion.columns(1).Cells
for each cell in rng
if trim(cell.value) < "" then
set bk = Workbooks.Open(cell.Value)
set rng1 = Cell.offset(0,1).Resize(1,51).SpecialCells(xlConst ants)
for each cell1 in rng1
if lcase(cell1.Value) = "all" then
bk.printout
exit for
else
if trim(cell1.Value) < "" then
bk.worksheets(cell1.Value).Printout
end if
end if
Next
End if
Next

Hopefully there are no sheets named All.

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hi Tom. Thanks for the response. Can I ask for one enhancement? I
have
a
few files that have 30 or so pages in them, ALL of which need to be
printed.
Is it possible to edit the code to also allow for the entry "All" in
column
B rather than naming each specific sheet, where that woul trigger excel
to
print all pages within that workbook?

Thanks so much for your help!!

"Tom Ogilvy" wrote in message
...
Dim rng as Range, rng1 as Range
Dim cell as Range, cell1 as Range
Dim bk as Workbook
set rng = Range("A1").CurrentRegion.columns(1)
for each cell in rng
if trim(cell.value) < "" then
set bk = Workbooks.Open(cell.Value)
set rng1 =
Cell.offset(0,1).Resize(1,51).SpecialCells(xlConst ants)
for each cell1 in rng1
if trim(cell1.Value) < "" then
bk.worksheets(cell1.Value).Printout
end if
Next
End if
Next

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi everyone. In column A I have the full path of a file. In
columns
B
through AZ I have sheet names within that file. How can I code
excel
to
open the file in A1, print the sheets in B1 through AZ1, then close
the
file, then move to the next row and do the same until all rows
containing
data in column A have been looped though? Thank you in advance.

PS - Some files may only have 2 sheets to be printed, so D1 through
AZ1
may
be blank.

Open file and print sheets based on data in cell
 

forgot to close the workbooks:

Dim rng as Range, rng1 as Range
Dim cell as Range, cell1 as Range
Dim bk as Workbook
set rng = Range("A1").CurrentRegion.columns(1).Cells
for each cell in rng
if trim(cell.value) < "" then
set bk = Workbooks.Open(cell.Value)
set rng1 = Cell.offset(0,1).Resize(1,51).SpecialCells(xlConst ants)
for each cell1 in rng1
if lcase(cell1.Value) = "all" then
bk.printout
exit for
else
if trim(cell1.Value) < "" then
bk.worksheets(cell1.Value).Printout
end if
end if
Next
bk.close SaveChanges:=False
End if
Next

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Thanks Tom!

"Tom Ogilvy" wrote in message
...
Dim rng as Range, rng1 as Range
Dim cell as Range, cell1 as Range
Dim bk as Workbook
set rng = Range("A1").CurrentRegion.columns(1).Cells
for each cell in rng
if trim(cell.value) < "" then
set bk = Workbooks.Open(cell.Value)
set rng1 = Cell.offset(0,1).Resize(1,51).SpecialCells(xlConst ants)
for each cell1 in rng1
if lcase(cell1.Value) = "all" then
bk.printout
exit for
else
if trim(cell1.Value) < "" then
bk.worksheets(cell1.Value).Printout
end if
end if
Next
End if
Next

Hopefully there are no sheets named All.

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hi Tom. Thanks for the response. Can I ask for one enhancement? I
have
a
few files that have 30 or so pages in them, ALL of which need to be
printed.
Is it possible to edit the code to also allow for the entry "All" in
column
B rather than naming each specific sheet, where that woul trigger
excel
to
print all pages within that workbook?

Thanks so much for your help!!

"Tom Ogilvy" wrote in message
...
Dim rng as Range, rng1 as Range
Dim cell as Range, cell1 as Range
Dim bk as Workbook
set rng = Range("A1").CurrentRegion.columns(1)
for each cell in rng
if trim(cell.value) < "" then
set bk = Workbooks.Open(cell.Value)
set rng1 =
Cell.offset(0,1).Resize(1,51).SpecialCells(xlConst ants)
for each cell1 in rng1
if trim(cell1.Value) < "" then
bk.worksheets(cell1.Value).Printout
end if
Next
End if
Next

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Hi everyone. In column A I have the full path of a file. In
columns
B
through AZ I have sheet names within that file. How can I code
excel
to
open the file in A1, print the sheets in B1 through AZ1, then
close
the
file, then move to the next row and do the same until all rows
containing
data in column A have been looped though? Thank you in advance.

PS - Some files may only have 2 sheets to be printed, so D1
through
AZ1
may
be blank.