I have a workbook containing 32 sheets, one (Printsheet) for printout
with a format & rest of 31 (1,2,3,4,5....31) for each day of a month
in another format.

In the print sheet, I would like to get values in cells from the sheet
that I select.
If I write 4 in print sheet, then I should get all the values as
mentioned in sheet 4 ans so on.

I also want that the sheet number that I input should be in date
format like if I input 1/9, then it shows September 01, 2007 & gets
values from sheet 1 and so on.

I have been guessin many formulas but.... : /

would appreciate a helpin hand.

Believe it or not, with just a little modification of a recorded macro you
could do this yourself to a large degree. If you're unfamiliar with
modifying macros or VBA, then you could record the basic process and others
here could help you adapt it to be generic.

I'd start on one of the sheets with data to be placed onto the printsheet.
Start recording a new macro. Then go through the process step by step
(carefully, might even want to practice once or twice first): copying as
much data as you can in one pass, choosing the print sheet, pasting it in
(use Edit | Paste Special with "values" checked initially to keep from
pasting formats and formulas also) return to the source sheet, copy more if
needed, back to print sheet to paste again until you're done with copy/paste
and finally hit the print icon. Stop recording the macro.

Now, first thing I'd do in editing the macro is to put the statement
Application.ScreenUpdating = False
right at the beginning of it so you don't get annoyed by all the screen
flashing going on as the jumping around in the workbook takes place.

The next thing you have to do is figure out how to tell it which sheet
you're copying from. You can save the name of the sheet you start out on
with 2 lines of code, they can be placed right after, or just ahead of that
line I gave you earlier:

Dim anySheetName As String
anySheetName = ActiveSheet.Name

Then look for all the places in the macro (or do a Find | Replace) where
there are statements like:
Worksheets("originalDayName").Select
and replace everything between the ( ) including the quote marks, with
anySheetName.

From then on, you choose one of your daily sheets and then run the macro and
it'll repeat the whole process for that particular worksheet.

This hasn't addressed the issue of turning a sheet number into a date, but
it would be nice to know if this has a chance of succeeding for you.

If your information covers different areas of the daily sheets, then it's
definitely going to take some more work, and we don't know enough about the
layout of your sheets to make an intelligent guess as to how to help. Need
to know rows/columns that need to be copied, which ones could vary in range
(# of rows increasing or decreasing from sheet to sheet, etc) befor anything
really tailored could be built.


"Sinner" wrote:

I have a workbook containing 32 sheets, one (Printsheet) for printout
with a format & rest of 31 (1,2,3,4,5....31) for each day of a month
in another format.

In the print sheet, I would like to get values in cells from the sheet
that I select.
If I write 4 in print sheet, then I should get all the values as
mentioned in sheet 4 ans so on.

I also want that the sheet number that I input should be in date
format like if I input 1/9, then it shows September 01, 2007 & gets
values from sheet 1 and so on.

I have been guessin many formulas but.... : /

would appreciate a helpin hand.

JLatham thankyou for your input. Sure VBA is a smart solution - no
doubt about that :)

To make things easy, I just want to have cell D2 value from sheet of
my choice in E7 of print sheet which can be done with formula. I can
then adjust the formula for addresses.

Thanks once again for the reply.
---------------------------------------------------------------------------------------------------------------------

On Sep 4, 4:02 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
Believe it or not, with just a little modification of a recorded macro you
could do this yourself to a large degree. If you're unfamiliar with
modifying macros or VBA, then you could record the basic process and others
here could help you adapt it to be generic.

I'd start on one of the sheets with data to be placed onto the printsheet.
Start recording a new macro. Then go through the process step by step
(carefully, might even want to practice once or twice first): copying as
much data as you can in one pass, choosing the print sheet, pasting it in
(use Edit | Paste Special with "values" checked initially to keep from
pasting formats and formulas also) return to the source sheet, copy more if
needed, back to print sheet to paste again until you're done with copy/paste
and finally hit the print icon. Stop recording the macro.

Now, first thing I'd do in editing the macro is to put the statement
Application.ScreenUpdating = False
right at the beginning of it so you don't get annoyed by all the screen
flashing going on as the jumping around in the workbook takes place.

The next thing you have to do is figure out how to tell it which sheet
you're copying from. You can save the name of the sheet you start out on
with 2 lines of code, they can be placed right after, or just ahead of that
line I gave you earlier:

Dim anySheetName As String
anySheetName = ActiveSheet.Name

Then look for all the places in the macro (or do a Find | Replace) where
there are statements like:
Worksheets("originalDayName").Select
and replace everything between the ( ) including the quote marks, with
anySheetName.

From then on, you choose one of your daily sheets and then run the macro and
it'll repeat the whole process for that particular worksheet.

This hasn't addressed the issue of turning a sheet number into a date, but
it would be nice to know if this has a chance of succeeding for you.

If your information covers different areas of the daily sheets, then it's
definitely going to take some more work, and we don't know enough about the
layout of your sheets to make an intelligent guess as to how to help. Need
to know rows/columns that need to be copied, which ones could vary in range
(# of rows increasing or decreasing from sheet to sheet, etc) befor anything
really tailored could be built.



"Sinner" wrote:
I have a workbook containing 32 sheets, one (Printsheet) for printout
with a format & rest of 31 (1,2,3,4,5....31) for each day of a month
in another format.

In the print sheet, I would like to get values in cells from the sheet
that I select.
If I write 4 in print sheet, then I should get all the values as
mentioned in sheet 4 ans so on.

I also want that the sheet number that I input should be in date
format like if I input 1/9, then it shows September 01, 2007 & gets
values from sheet 1 and so on.

I have been guessin many formulas but.... : /

would appreciate a helpin hand.- Hide quoted text -

- Show quoted text -

I'm not sure I completely understand at this point. You have 1 or many cells
to move data from other sheets into the print sheet?

It sounds a bit like you need an INDIRECT() formula that can have different
sheet references, and I think you'll find help on setting that up he
http://www.contextures.com/xlFunctions05.html
Scroll down to the section titled
Create a Reference To a Different Sheet

Is that any help?

"Sinner" wrote:

JLatham thankyou for your input. Sure VBA is a smart solution - no
doubt about that :)

To make things easy, I just want to have cell D2 value from sheet of
my choice in E7 of print sheet which can be done with formula. I can
then adjust the formula for addresses.

Thanks once again for the reply.
---------------------------------------------------------------------------------------------------------------------

On Sep 4, 4:02 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
Believe it or not, with just a little modification of a recorded macro you
could do this yourself to a large degree. If you're unfamiliar with
modifying macros or VBA, then you could record the basic process and others
here could help you adapt it to be generic.

I'd start on one of the sheets with data to be placed onto the printsheet.
Start recording a new macro. Then go through the process step by step
(carefully, might even want to practice once or twice first): copying as
much data as you can in one pass, choosing the print sheet, pasting it in
(use Edit | Paste Special with "values" checked initially to keep from
pasting formats and formulas also) return to the source sheet, copy more if
needed, back to print sheet to paste again until you're done with copy/paste
and finally hit the print icon. Stop recording the macro.

Now, first thing I'd do in editing the macro is to put the statement
Application.ScreenUpdating = False
right at the beginning of it so you don't get annoyed by all the screen
flashing going on as the jumping around in the workbook takes place.

The next thing you have to do is figure out how to tell it which sheet
you're copying from. You can save the name of the sheet you start out on
with 2 lines of code, they can be placed right after, or just ahead of that
line I gave you earlier:

Dim anySheetName As String
anySheetName = ActiveSheet.Name

Then look for all the places in the macro (or do a Find | Replace) where
there are statements like:
Worksheets("originalDayName").Select
and replace everything between the ( ) including the quote marks, with
anySheetName.

From then on, you choose one of your daily sheets and then run the macro and
it'll repeat the whole process for that particular worksheet.

This hasn't addressed the issue of turning a sheet number into a date, but
it would be nice to know if this has a chance of succeeding for you.

If your information covers different areas of the daily sheets, then it's
definitely going to take some more work, and we don't know enough about the
layout of your sheets to make an intelligent guess as to how to help. Need
to know rows/columns that need to be copied, which ones could vary in range
(# of rows increasing or decreasing from sheet to sheet, etc) befor anything
really tailored could be built.



"Sinner" wrote:
I have a workbook containing 32 sheets, one (Printsheet) for printout
with a format & rest of 31 (1,2,3,4,5....31) for each day of a month
in another format.

In the print sheet, I would like to get values in cells from the sheet
that I select.
If I write 4 in print sheet, then I should get all the values as
mentioned in sheet 4 ans so on.

I also want that the sheet number that I input should be in date
format like if I input 1/9, then it shows September 01, 2007 & gets
values from sheet 1 and so on.

I have been guessin many formulas but.... : /

would appreciate a helpin hand.- Hide quoted text -

- Show quoted text -

Yes JLatham, I had figured out tht before your post :)
Thanks for your reply. Really appreciate tht


Sinner


On Sep 4, 5:28 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
I'm not sure I completely understand at this point. You have 1 or many cells
to move data from other sheets into the print sheet?

It sounds a bit like you need an INDIRECT() formula that can have different
sheet references, and I think you'll find help on setting that up hehttp://www.contextures.com/xlFunctions05.html
Scroll down to the section titled
Create a Reference To a Different Sheet

Is that any help?



"Sinner" wrote:
JLatham thankyou for your input. Sure VBA is a smart solution - no
doubt about that :)

To make things easy, I just want to have cell D2 value from sheet of
my choice in E7 of print sheet which can be done with formula. I can
then adjust the formula for addresses.

Thanks once again for the reply.
---------------------------------------------------------------------------*------------------------------------------

On Sep 4, 4:02 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
Believe it or not, with just a little modification of a recorded macro you
could do this yourself to a large degree. If you're unfamiliar with
modifying macros or VBA, then you could record the basic process and others
here could help you adapt it to be generic.

I'd start on one of the sheets with data to be placed onto the printsheet.
Start recording a new macro. Then go through the process step by step
(carefully, might even want to practice once or twice first): copying as
much data as you can in one pass, choosing the print sheet, pasting it in
(use Edit | Paste Special with "values" checked initially to keep from
pasting formats and formulas also) return to the source sheet, copy more if
needed, back to print sheet to paste again until you're done with copy/paste
and finally hit the print icon. Stop recording the macro.

Now, first thing I'd do in editing the macro is to put the statement
Application.ScreenUpdating = False
right at the beginning of it so you don't get annoyed by all the screen
flashing going on as the jumping around in the workbook takes place.

The next thing you have to do is figure out how to tell it which sheet
you're copying from. You can save the name of the sheet you start out on
with 2 lines of code, they can be placed right after, or just ahead of that
line I gave you earlier:

Dim anySheetName As String
anySheetName = ActiveSheet.Name

Then look for all the places in the macro (or do a Find | Replace) where
there are statements like:
Worksheets("originalDayName").Select
and replace everything between the ( ) including the quote marks, with
anySheetName.

From then on, you choose one of your daily sheets and then run the macro and
it'll repeat the whole process for that particular worksheet.

This hasn't addressed the issue of turning a sheet number into a date, but
it would be nice to know if this has a chance of succeeding for you.

If your information covers different areas of the daily sheets, then it's
definitely going to take some more work, and we don't know enough about the
layout of your sheets to make an intelligent guess as to how to help. Need
to know rows/columns that need to be copied, which ones could vary in range
(# of rows increasing or decreasing from sheet to sheet, etc) befor anything
really tailored could be built.

"Sinner" wrote:
I have a workbook containing 32 sheets, one (Printsheet) for printout
with a format & rest of 31 (1,2,3,4,5....31) for each day of a month
in another format.

In the print sheet, I would like to get values in cells from the sheet
that I select.
If I write 4 in print sheet, then I should get all the values as
mentioned in sheet 4 ans so on.

I also want that the sheet number that I input should be in date
format like if I input 1/9, then it shows September 01, 2007 & gets
values from sheet 1 and so on.

I have been guessin many formulas but.... : /

would appreciate a helpin hand.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You're welcome. Did the info at Debra's site help? I hope. If not, we can
dig into it deeper.

"Sinner" wrote:

Yes JLatham, I had figured out tht before your post :)
Thanks for your reply. Really appreciate tht


Sinner


On Sep 4, 5:28 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
I'm not sure I completely understand at this point. You have 1 or many cells
to move data from other sheets into the print sheet?

It sounds a bit like you need an INDIRECT() formula that can have different
sheet references, and I think you'll find help on setting that up hehttp://www.contextures.com/xlFunctions05.html
Scroll down to the section titled
Create a Reference To a Different Sheet

Is that any help?



"Sinner" wrote:
JLatham thankyou for your input. Sure VBA is a smart solution - no
doubt about that :)

To make things easy, I just want to have cell D2 value from sheet of
my choice in E7 of print sheet which can be done with formula. I can
then adjust the formula for addresses.

Thanks once again for the reply.
----------------------------------------------------------------------------------------------------------------------

On Sep 4, 4:02 am, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote:
Believe it or not, with just a little modification of a recorded macro you
could do this yourself to a large degree. If you're unfamiliar with
modifying macros or VBA, then you could record the basic process and others
here could help you adapt it to be generic.

I'd start on one of the sheets with data to be placed onto the printsheet.
Start recording a new macro. Then go through the process step by step
(carefully, might even want to practice once or twice first): copying as
much data as you can in one pass, choosing the print sheet, pasting it in
(use Edit | Paste Special with "values" checked initially to keep from
pasting formats and formulas also) return to the source sheet, copy more if
needed, back to print sheet to paste again until you're done with copy/paste
and finally hit the print icon. Stop recording the macro.

Now, first thing I'd do in editing the macro is to put the statement
Application.ScreenUpdating = False
right at the beginning of it so you don't get annoyed by all the screen
flashing going on as the jumping around in the workbook takes place.

The next thing you have to do is figure out how to tell it which sheet
you're copying from. You can save the name of the sheet you start out on
with 2 lines of code, they can be placed right after, or just ahead of that
line I gave you earlier:

Dim anySheetName As String
anySheetName = ActiveSheet.Name

Then look for all the places in the macro (or do a Find | Replace) where
there are statements like:
Worksheets("originalDayName").Select
and replace everything between the ( ) including the quote marks, with
anySheetName.

From then on, you choose one of your daily sheets and then run the macro and
it'll repeat the whole process for that particular worksheet.

This hasn't addressed the issue of turning a sheet number into a date, but
it would be nice to know if this has a chance of succeeding for you.

If your information covers different areas of the daily sheets, then it's
definitely going to take some more work, and we don't know enough about the
layout of your sheets to make an intelligent guess as to how to help. Need
to know rows/columns that need to be copied, which ones could vary in range
(# of rows increasing or decreasing from sheet to sheet, etc) befor anything
really tailored could be built.

"Sinner" wrote:
I have a workbook containing 32 sheets, one (Printsheet) for printout
with a format & rest of 31 (1,2,3,4,5....31) for each day of a month
in another format.

In the print sheet, I would like to get values in cells from the sheet
that I select.
If I write 4 in print sheet, then I should get all the values as
mentioned in sheet 4 ans so on.

I also want that the sheet number that I input should be in date
format like if I input 1/9, then it shows September 01, 2007 & gets
values from sheet 1 and so on.

I have been guessin many formulas but.... : /

would appreciate a helpin hand.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -