Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
dEAR aLL,
Can anybody help me please.... Here is a simple macro which copy formulas from B9 till B33 Is there any way I can name the cell B40 as "END" and copy the formulas until END if so what I should add more in this code? Sub Macro1() ' Macro1 Macro Range("B9").Select Selection.AutoFill Destination:=Range("B9:B33"), Type:=xlFillDefault Range("B9:B33").Select End Sub Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
By END I am assuming that you want to autofill down as far as the last used
row in another column. Is this correct? If so, then you can find the last used row in an adjacent column and concatenate that in the range as per the following. The code finds the last used row in column A. Edit that to another column if necessary. Dim lastRow As Long 'Edit the "A" in the following line to match 'the column to test for last row lastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("B9").Select Selection.AutoFill Destination:=Range("B9:B" & lastRow), Type:=xlFillDefault Range("B9:B" & lastRow).Select -- Regards, OssieMac " VLOOKUP fORMULA" wrote: dEAR aLL, Can anybody help me please.... Here is a simple macro which copy formulas from B9 till B33 Is there any way I can name the cell B40 as "END" and copy the formulas until END if so what I should add more in this code? Sub Macro1() ' Macro1 Macro Range("B9").Select Selection.AutoFill Destination:=Range("B9:B33"), Type:=xlFillDefault Range("B9:B33").Select End Sub Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
Dear Sir,
Regarding this answer. This is working fine. I pasted this code in Macro code. Always giving a pop up window stating that "The flwg code cannot be saved in macro free work book, *VB project to save a file with htese features, click No, and then choose a macro-enabled file type in the file type list. Can you tell me exactly where to paste this code, Can you show me how. " VLOOKUP fORMULA" wrote: dEAR aLL, Can anybody help me please.... Here is a simple macro which copy formulas from B9 till B33 Is there any way I can name the cell B40 as "END" and copy the formulas until END if so what I should add more in this code? Sub Macro1() ' Macro1 Macro Range("B9").Select Selection.AutoFill Destination:=Range("B9:B33"), Type:=xlFillDefault Range("B9:B33").Select End Sub Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
To get that message then I am assuming that you are using xl2007 so the
instructions are for xl2007. If this is not correct then please let me know what version of xl you are using. You need to select Save as. The following instructions tell you how to do this:- Select the Microsoft button (Big button top left of screen) Click Save as In the Save dialog box in the Save as type field select Excel Macro-Enabled Workbook (*.xlsm) In the File Name field either leave the name that is there or insert another one. (If you insert a new name then there is no need to insert the extension but if you do then it must be .xlsm) Click Save and you are done. However, you could now have 2 files with similar names but one with xlsx extension and one with xlsm extension. You use the xlsm one (or macro enabled one) You also need to set the options to allow macros to run as follows. Select the Microsoft button (Big button top left of screen) Select Excel options (towards bottom right of dialog box) Select Trust Center (Left column of dialog box) Select Trust Center settings (Towards middle right of dialog box) Select Macro Settings (Left column) Select Disable all macros with notification Click OK and then OK again. Now when you attempt to open an xl file with a macro you will be notified that a macro exists and ask if you want to allow macros to run (or something like that) so select Yes or allow or whatever. (I am not sure of the actual message because I have my computer set up to allow all to run from a trusted location and I never see the message). I am happy for you that my previous answer was what you were looking for and I hope this helps you to complete it. Feel free to get back to me if you are still having problems. -- Regards, OssieMac " VLOOKUP fORMULA" wrote: Dear Sir, Regarding this answer. This is working fine. I pasted this code in Macro code. Always giving a pop up window stating that "The flwg code cannot be saved in macro free work book, *VB project to save a file with htese features, click No, and then choose a macro-enabled file type in the file type list. Can you tell me exactly where to paste this code, Can you show me how. " VLOOKUP fORMULA" wrote: dEAR aLL, Can anybody help me please.... Here is a simple macro which copy formulas from B9 till B33 Is there any way I can name the cell B40 as "END" and copy the formulas until END if so what I should add more in this code? Sub Macro1() ' Macro1 Macro Range("B9").Select Selection.AutoFill Destination:=Range("B9:B33"), Type:=xlFillDefault Range("B9:B33").Select End Sub Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro
I works perfect and thanks OssieMac for spending your valuable time for me.
"OssieMac" wrote: To get that message then I am assuming that you are using xl2007 so the instructions are for xl2007. If this is not correct then please let me know what version of xl you are using. You need to select Save as. The following instructions tell you how to do this:- Select the Microsoft button (Big button top left of screen) Click Save as In the Save dialog box in the Save as type field select Excel Macro-Enabled Workbook (*.xlsm) In the File Name field either leave the name that is there or insert another one. (If you insert a new name then there is no need to insert the extension but if you do then it must be .xlsm) Click Save and you are done. However, you could now have 2 files with similar names but one with xlsx extension and one with xlsm extension. You use the xlsm one (or macro enabled one) You also need to set the options to allow macros to run as follows. Select the Microsoft button (Big button top left of screen) Select Excel options (towards bottom right of dialog box) Select Trust Center (Left column of dialog box) Select Trust Center settings (Towards middle right of dialog box) Select Macro Settings (Left column) Select Disable all macros with notification Click OK and then OK again. Now when you attempt to open an xl file with a macro you will be notified that a macro exists and ask if you want to allow macros to run (or something like that) so select Yes or allow or whatever. (I am not sure of the actual message because I have my computer set up to allow all to run from a trusted location and I never see the message). I am happy for you that my previous answer was what you were looking for and I hope this helps you to complete it. Feel free to get back to me if you are still having problems. -- Regards, OssieMac " VLOOKUP fORMULA" wrote: Dear Sir, Regarding this answer. This is working fine. I pasted this code in Macro code. Always giving a pop up window stating that "The flwg code cannot be saved in macro free work book, *VB project to save a file with htese features, click No, and then choose a macro-enabled file type in the file type list. Can you tell me exactly where to paste this code, Can you show me how. " VLOOKUP fORMULA" wrote: dEAR aLL, Can anybody help me please.... Here is a simple macro which copy formulas from B9 till B33 Is there any way I can name the cell B40 as "END" and copy the formulas until END if so what I should add more in this code? Sub Macro1() ' Macro1 Macro Range("B9").Select Selection.AutoFill Destination:=Range("B9:B33"), Type:=xlFillDefault Range("B9:B33").Select End Sub Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
macro to delete entire rows when column A is blank ...a quick macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |