Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 different macro questions
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, -- inthestands |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 different macro questions
#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. inthestands wrote: 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, -- inthestands -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 questions: Using solver in Macro AND converting to Word document to print A3 | Excel Discussion (Misc queries) | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Where can I post Macro questions? | Excel Worksheet Functions | |||
Date macro | Excel Discussion (Misc queries) | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |