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.

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(xlConstants)
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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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(xlConstants)
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
> >
>
>

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(xlConstants)
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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(xlConstants)
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> > >
> >
> >
>
>

Thanks Tom!

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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(xlConstants)
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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(xlConstants)
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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(xlConstants)
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" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom!
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > 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(xlConstants)
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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(xlConstants)
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>