Macros within Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Coni - 17 Mar 2008 16:05 GMT

Is it possible to do a macro to update information between the following
situation.
1 - Journal Spreadsheet - same every week - needs to be updated in cells J2,
J5, J8, etc. - every 3rd cell with information from spreadsheet 2.
2 - Payroll Spreadsheet - different every week - information in column H -
cells H13, H14, H15 update cells in spreadsheet 1 - i.e. H13 to J2, H14 to
J5, H15 to J8, etc.

When the new payroll spreadsheet (spreadsheet 3) comes into play, it has a
different name because of the date i.e. Payroll 2-15-08 - my macro only works
from Spreadsheet 1 to Spreadsheet 2 - when Spreadsheet 3 comes into play - is
there a way to do a macro to use this new information to go into Spreadsheet
1 - the Journal? Can this be done when it has a new name (different date)?

I know about the + and = signs to update/change information from one
spreadsheet to another - that is how I wrote my first macro. The cells we
need to use for the upated information are in the same location every week.
Spreadsheet 3 creates the problem....

Or is there a better way? Thanks for any input!

Signature

Coni

Reply to this Message

Pete_UK - 18 Mar 2008 11:00 GMT

If you post your macro code we will be in a better position to assist
you.

Generally speaking, you will have lines in your code containing
phrases like:

Sheets("Sheet3") etc

and you will need to change these to:

Sheets(my_sheet)

where my_sheet is a text variable that is built up into the sheet name
that you want to reference.

Hope this helps.

Pete

> Is it possible to do a macro to update information between the following
> situation.
[quoted text clipped - 18 lines]
> --
> Coni

Reply to this Message

Coni - 18 Mar 2008 14:24 GMT

Hi Pete,

Thank you for your reply. My macro looks like this...and this is just how
it recorded when I did this...

='[TEST 1 - Payroll - blank.xlsx] Sheet 1'!$H$13

then, ...$H$14 - $H$15, etc.

I do not know what I am doing! But have been requested to research this and
nobody in my office is familiar either! With your response, it seems I need
to change Sheet 1 to my_sheet? Then, when you say this is a test variable
that is built up into the sheet name that you want to reference...such as?
Can you give me an example?

I really appreciate your time. Thank you for dealing with Macro 101!

Signature

Coni

> If you post your macro code we will be in a better position to assist
> you.
[quoted text clipped - 37 lines]
> > --
> > Coni

Reply to this Message

Pete_UK - 19 Mar 2008 02:04 GMT

I think you are confusing the terminology. What you have is a formula,
not a macro, and the formula will bring data from cell H13 in Sheet1
of the workbook (file) called TEST 1 - Payroll - blank.xlsx. It is
also clear from this that you are using Excel 2007.

What you need to do is to describe the layout of your data in the
three sheets (are these all in different files?) and then describe
what it is you want to do - let the respondents here work out how to
do it.

Hope this helps.

Pete

> Hi Pete,
>
[quoted text clipped - 58 lines]
>
> - Show quoted text -

Reply to this Message

Coni - 20 Mar 2008 17:06 GMT

Hi Pete!

Thank you for your reply. I thought I described the layout of my data in my
first request, but here it is.

There are 3 different spreadsheets/files - not "sheets" within one
spreadsheet. The 1st spreadsheet is the only one that needs updated - from
the weekly payroll spreadsheets...
1. "Journal Spreadsheet" - same every week - needs to be updated in cells
J2, J4, J8, etc. - every 3rd cell with information from spreadsheet 2, then
the next payroll week - spreadsheet 3, etc.
2. "Payroll Spreadsheet" - different payroll total every week - information
in column H - cells H13, H14, H14 update cells in the "Journal Spreadsheet"
- i.e. H13 to J2, H14 to J5, H15 to J8. This update continues through H20.
3. "Payroll Spreadsheet" - New payroll - new spreadsheet - it has a
different name then #2 because of the date (i.e. Payroll 2-15-08, next week
Payroll 2-22-08, etc.)

I only know how to write "formulas" according to your description (and I
appreciate the correct terminology!), the + and = signs, to exchange
information from 2 spreadsheets. Is there a way to do a macro to use this
new information (always the same cells - H13 to J2, H14 to J5, etc) to go
into Spreadsheet 1 - "Journal Spreadsheet" - at the end of each weekly
payroll?

I don't know how to describe it any other way? Or maybe there is a better
way to accomplish this project? Do you have any more questions?

Signature

Coni

> I think you are confusing the terminology. What you have is a formula,
> not a macro, and the formula will bring data from cell H13 in Sheet1
[quoted text clipped - 72 lines]
> >
> > - Show quoted text -

Reply to this Message

Rate this thread: