Macro

  • Thread starter Thread starter Gaffnr
  • Start date Start date
G

Gaffnr

Hi All,
Question re. macros. Im not a programmer thats writes them but someone who
simply records and plays. Got a question about something....

Lets say I have formulae in cells D2, E2 and F2.
I have data in A2, B2 and C2 down to A10, B10 and C10.

Record Macro.
I Copy formulae in D2,E2 and F2 down to D10, E10, F10 top match the data rows.
This is fine when I run the macro where the data is always to row 10.
what about if my data in a,B,c goes down to Row
a) row 20 - i am left with 10 rows where the formulae does not copy
b) row 5 - I have rows of formula not needed (as i pivot table the results
this is a pain).

What I want it every time i run the macro to copy the formulae down to the
last row of data, where everit may be.

When i do it manaully, i get the mouse pointer into the bottom righ corner
of cell F2 (where D2,E2 and F2 are all highlighted), the pinter turns into a
small black cross and i double click to copy the formula down to the last row
where there is data in c. This is what I want the macro to achieve. I tried
copying this mouse stroke but it always on running goes downt to the same row
every time.
Thanks
rob
 
Hi Rob

Depending upon the version of XL you are running, you may not need to do
this.
For XL2003 there is a feature called Data>Lists
If you click within your used table range and choose Data>List then Excel
will create an ever expanding table, where any formulae are automatically
created on lines added.
This List can be used as the source data for the Pivot Table and it will
grow automatically to include the last row used.

In XL2007, the feature is further improved and can be found on the Insert
tab, as Insert>Table.

If you are using XL2002 or lower post back and we can provide you with a
macro solution.
 
Hi Roger
Thanks for your superfast reply. Im not sure I understand yet what you
wrote so let me tell you about my end goal to see if what you propose will
help.

The data is populated via an external source, copied and pasted into Excel.
The macro I want is only part of a larger macro. The goal is to put the
ssheet on a shared drive so that my organisation can run this independently
with their own data.

So, they will run the external source report I create.
They will copy and paste their data.
They will then run a macro from a button in the spreadsheet to copy the
formulae down to last row, do some other stuff and create pivot.

The next user will repeat and their data could be more or less rows every
time.

We are using Excel 2003 SP 2. If your first proposal will work, thats
sounds great.
A little more background would be great.

Thanks again
Rob
 
Hi Rob

Yes it will work.
Just so you can understand the way it works create a the following on a new
sheet
A1 name
B1 Value1
C1 Value2
D1 Formula1
E1 Formula2

In D1 enter = B1*C1
in E1 enter = D1*E1
Enter some values in A2, B2 and C2
Place cursor in A1, Data>List>Create List>click my data has header row>OK

Now on sheet2 enter some appropriate values in A B and C for a few rows.
Copy this data>move to Sheet1 and paste the data into A3 (where there should
be a blue asterisk) and you will see that the formulae automatically get
entered in columns D and E.

If there is no asterisk showing in the next available row of the table in
Sheet1, click on column A of the last row entered, and a blue line will
surround the whole list, including the first blank line following the data,
where the asterisk will appear.

Now if you place your cursor in any cell on the list and choose Data>Pivot
Table>Finish it will use the List as the source, and it will grow
dynamically as more rows of data are added.
 
Hi Roger
Ive tested this but it wont for me - let me explain.
Your idea works for addendums to a list but not how we will be doing it...

Region Spains data may contain 1000 rows. They will paste it in over the
top of existing data,starting in row 2. any prior data is not needed.

Next, Region Germany data contains 500 rows they paste over the top of the
spain data. That leaves 500 rows of Spanish data to remove/

What im getting at is that its not an ongoing list, but changes from region
to region and from within that, the rows differ from month to month.

So, idea is to have a macro that does :

step 1 - clear out all formulae apart from row 2 (remember the last row of
formulae could differ every time the macro is run)
step 2 - paste in the date from another excel SSheet on the same shared drive
step 3 - copy the formulae in row 2 down to the last row
Step 4 - do other stuff
Step 5 - create the pivot

tks re. the list. I never knew of that and take it on board re, other
projects

Nect region France data contains 2500 rows. Thats 2000 more rows than the
last set of data used.



--
Rob Gaffney


Roger Govier said:
Hi Rob

Yes it will work.
Just so you can understand the way it works create a the following on a new
sheet
A1 name
B1 Value1
C1 Value2
D1 Formula1
E1 Formula2

In D1 enter = B1*C1
in E1 enter = D1*E1
Enter some values in A2, B2 and C2
Place cursor in A1, Data>List>Create List>click my data has header row>OK

Now on sheet2 enter some appropriate values in A B and C for a few rows.
Copy this data>move to Sheet1 and paste the data into A3 (where there should
be a blue asterisk) and you will see that the formulae automatically get
entered in columns D and E.

If there is no asterisk showing in the next available row of the table in
Sheet1, click on column A of the last row entered, and a blue line will
surround the whole list, including the first blank line following the data,
where the asterisk will appear.

Now if you place your cursor in any cell on the list and choose Data>Pivot
Table>Finish it will use the List as the source, and it will grow
dynamically as more rows of data are added.
--

Regards
Roger Govier

Gaffnr said:
Hi Roger
Thanks for your superfast reply. Im not sure I understand yet what you
wrote so let me tell you about my end goal to see if what you propose will
help.

The data is populated via an external source, copied and pasted into
Excel.
The macro I want is only part of a larger macro. The goal is to put the
ssheet on a shared drive so that my organisation can run this
independently
with their own data.

So, they will run the external source report I create.
They will copy and paste their data.
They will then run a macro from a button in the spreadsheet to copy the
formulae down to last row, do some other stuff and create pivot.

The next user will repeat and their data could be more or less rows every
time.

We are using Excel 2003 SP 2. If your first proposal will work, thats
sounds great.
A little more background would be great.

Thanks again
Rob
Click to expand...
Click to expand...
 
Sub Auto_Fill()
Dim Lrow As Long
With ActiveSheet
Lrow = Range("A" & Rows.Count).End(xlUp).Row
Range("D2:F" & Lrow).FillDown
End With
End Sub


Gord Dibben MS Excel MVP
 
Back
Top