2 different macro questions

[Guest]

1st) i download 4 different sets of data from our stores on a nightly basis.
I insert that days sales data into a template. One store may only have 50
transactions where another may have 200. The template has some preset
formulas in them to calculate margins and costs to the transactions. When I
sort the new data the formula line may be anywhere from line 400 to line
1000. How can i insert a macro so it knows to look for the formula line and
then copy down to include all of the transactions? I think has something to
do with absolute references versus relative references, but I have not
figured it out yet. Thanks for the help

2nd) When I first open up to run a macro the little recorder icon remains on
the page. But if I screw up and need to rerun it, it disappears and then I
have to go back into tools to stop the recorder. Why is this happening, and
how do I keep the recorder on the page?

Thanks in advance,

 

[Dave Peterson]

#1. Maybe you can use a column in the imported data to get the last row.

Dim LastRow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

Say your formula is in B1 and needs to be copied down.

with activesheet
.range("B2:B" & lastrow).formular1c1 = .range("b1").formular1c1
end with

=====
Or maybe using autofill would work:

With ActiveSheet
.Range("B1").AutoFill _
Destination:=.Range("B1:B" & lastrow)
end with

#2. It sounds like you're closing that recording toolbar by using the X. If it
disappears again, just start recording a new macro.

Then use Tools|Customize|Toolbars tab to show the "stop recording" toolbar.

Then don't use that X to close it again! <vbg>. Click the stop recording
button.