Insert Code

I have some VBA code that I would like to insert into an excel worksheet 

With ActiveCell.Columns
   .EntireColumn.Copy
   .EntireColumn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, 
SkipBlanks _
       :=False, Transpose:=False

End With
Application.CutCopyMode = False

I thought all I would have to do is create a macro, use the macro editor and
paste in this code instead and run that macro, and this code would run
instead.....Thats not working.......how do i run this code??


-- 
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-new/200510/1
0
Derek
10/17/2005 12:42:13 AM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
472 Views

Similar Articles

[PageSpeed] 0

Try this ..

Press Alt+F11 to go to VBE
Click Insert > Module
Paste the sub* below in the code window
*containing your code

Press Alt+Q to get back to Excel

Test it out on a column with some formulas in your test sheet:
Select any cell in the col, press Alt+F8,
select "FreezeColumn" > Run
(The code will perform an "in-place"
copy > paste special > values on the col,
effectively killing all formulas in the col)

Sub FreezeColumn()
    With ActiveCell.Columns
       .EntireColumn.Copy
       .EntireColumn.PasteSpecial Paste:=xlPasteValues, _
       Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    End With
    Application.CutCopyMode = False
End Sub
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Derek Y via OfficeKB.com" <u13919@uwe> wrote in message
news:55f2dcfb20573@uwe...
> I have some VBA code that I would like to insert into an excel worksheet
>
> With ActiveCell.Columns
>    .EntireColumn.Copy
>    .EntireColumn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
>        :=False, Transpose:=False
>
> End With
> Application.CutCopyMode = False
>
> I thought all I would have to do is create a macro, use the macro editor
and
> paste in this code instead and run that macro, and this code would run
> instead.....Thats not working.......how do i run this code??
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200510/1


0
demechanik (4694)
10/17/2005 1:15:27 AM
THANK YOU SOOO MUCH, i didn't have SUB and END SUB that was my problem........
thanks sooo very much!

Max wrote:
>Try this ..
>
>Press Alt+F11 to go to VBE
>Click Insert > Module
>Paste the sub* below in the code window
>*containing your code
>
>Press Alt+Q to get back to Excel
>
>Test it out on a column with some formulas in your test sheet:
>Select any cell in the col, press Alt+F8,
>select "FreezeColumn" > Run
>(The code will perform an "in-place"
>copy > paste special > values on the col,
>effectively killing all formulas in the col)
>
>Sub FreezeColumn()
>    With ActiveCell.Columns
>       .EntireColumn.Copy
>       .EntireColumn.PasteSpecial Paste:=xlPasteValues, _
>       Operation:=xlNone, SkipBlanks:=False, Transpose:=False
>    End With
>    Application.CutCopyMode = False
>End Sub
>--
>Rgds
>Max
>xl 97
>---
>Singapore, GMT+8
>xdemechanik
>http://savefile.com/projects/236895
>--
>> I have some VBA code that I would like to insert into an excel worksheet
>>
>[quoted text clipped - 10 lines]
>> paste in this code instead and run that macro, and this code would run
>> instead.....Thats not working.......how do i run this code??


-- 
Message posted via http://www.officekb.com
0
Derek
10/17/2005 2:15:18 AM
Glad you found out what was missing <g> !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Derek Y via OfficeKB.com" <u13919@uwe> wrote in message
news:55f3ad2e0b6f3@uwe...
> THANK YOU SOOO MUCH, i didn't have SUB and END SUB that was my
problem........
> thanks sooo very much!


0
demechanik (4694)
10/17/2005 2:48:56 AM
Reply:

Similar Artilces:

Fixed Asset Import
I am trying to import Fixed Asset records using Integration Manager 10.0 and I am running into the following error message: Description = The UpdateIfExists parameter is set to 0 and you are attempting to integrate a record that already exists in the FA00100 table I would like to be able to update. Does anyone know how I can change this to allow for insert and update functionality? Unfortunately the eConnect Requester setup tool does not have that table as an option to update. Thanks, Craig Craig: I don't use integration manager so I apologize if this is diffferent but I do use ...

Bug Rep: Excel 2007 .xlsm file not saving commented codes
since i've tested this agaist 2 different copies of Excel 2007 fresh install on different machines at home, i think it's clearly a bug. ok, in order to save vba codes now in excel 2007, i'd have to save files in the .xlsm format. that's cool. everything been working sweet, until... spent 4 long days busting my butt during the new year, missed all the nice parties to rush this stupid project out, and 2 hours being scream at by my boss over skype kinda ate my heart out. how can such a simple mistake be made? very very easy >:Errrrrrrr go on any sheet that has codes ...

Using A Macro/VBA code to re-set formulas
Hi can any one help me with a Macro/VBA code to re-set formulas in pre-defined set of cells i.e. in my case C14:C40 At present I have a copy of the Formulas in another part of th worksheet and when I want to reset the worksheet (as the user can ove right the formulas as the formula is based on either the sum of anothe two cells or the users input) I use a Macro to copy and paste (special the formulas. What I require is a Macro/VBA Code which already has the formula store in the actual Macro/VBA code i.e. so they are not stored as copy in m worksheet Ay help would be greatly appreciated T...

Picture inserted from file cannot be aligned
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Hello <br><br>I have inserted a .PNG picture from file into a Word doc, however the option to Order > Align is greyed out, and the only way I can find to move it to centre is by moving the ruler. <br><br>I would be grateful for help. <br> Thanks > Hello <br> > <br> > I have inserted a .PNG picture from file into a Word doc, however the option to Order > Align is greyed out, and the only way I can find to move it to centre is by moving the ruler. <br...

slow code
i've made a code that searches one optional column for duplicates and deletes 2-6 cells in the same row as a duplicate data is found. I'm using this code a lot and my problem is that it can take very long time for the code to run, it can take 1 hour or more if i'm searching like 15000 rows. I'm going to the last cell in my row and uses the countif command to determine if there are duplicates, if not then i go to the cell above and run an new countif. is there a faster way to do this? jocke ------------------------------------------------ ~~ Message posted from http://www...

Splitting code to modules ... HOW ?
I have build several Excel VBA applictions, but many of them use som same code and functions. I like to put the code into ONE base module. How must I create the module, export it ? And how to refer to it in al the programs I like to use the base module ?? Roel -- Message posted from http://www.ExcelForum.com Roel If the macros are in several workbooks copy them into the same book and save this as Personal.xls The personal workbook may not be visible when you start Excel but the macros will be available to all workbooks. Alteratively, save the macro as an addin e.g. "myFile.xla&...

Colour code from different servers
-- I collect mail from 2 different servers and I was wondering if it is possible to have them show in the IN BOX in different colours. I know I can select different senders by colour but I want to show all senders that came from each server. Can anybody help Pete A "Pete A" <PeteA@discussions.microsoft.com> wrote in message news:EB77DCA6-A07E-47E9-AD04-62C8B6BC1193@microsoft.com... > > -- I collect mail from 2 different servers and I was wondering if it is > possible to have them show in the IN BOX in different colours. I know I > can > select different se...

how do i change or make this macro??? pictures and code included
i need to either make this from scratch or change it to suit my needs. Firstly a link to the template it is from http://www.exinfm.com/excel%20files/FY2000WhatIf.xls the code is Sub projection() Dim revgrowth As Single, costpercent As Single, smgrowth As Single devgrowth As Single, gagrowth As Single Dim intincome As Integer, noncontitems As Integer, otherexps A Integer Dim taxrate As Single, avgshares As Integer 'dialog box gets displayed With DialogSheets("dialog3") If .Show = False Then Exit Sub End If revgrowth = .EditBoxes(1).Text costpercent = .EditBoxes(2).Text smgrow...

Hiding and Unhiding Forms In Code
This is what I am trying to do. I hide a form and opened another form using a button: Me.Visible=False DoCmd.Open "Select Report Type", acNormal Now I want to unhide the form again using a button on the second form, I tried: If Forms![Inscope Site Summary].IsVisible=False Then Forms![Inscope Site Summary].Visible=true End If But this didn't work. I am stuck. Any help will be greatly appreciated. Thank you. Here is an example of using the IsVisible property (straight from your friendly Access help file); If Me!CategoryID.IsVisible Then Me!Line0.Visible = Tr...

Insert blank rows
Rather than using the control button to select every other row, which can be very time consuming when you have more than 100 rows, I am trying to figure out how to auto-insert blank rows between pre-populated rows of information. Deb, Try this macro. ALT+F11 to open VB editor. Right click 'ThisWorkbook' and insert module and paste the code in on the right . Change the sheet name to your sheet and run the code Sub insert_rows() Dim ws As Worksheet, x as long Set ws = Sheets("Sheet3") lastrow = ws.Cells(Cells.Rows.Count, "A").End(xlUp).Row For x = ...

Picture missing and unable to re-insert?
Hi. I've been putting together a powerpoint 2003 slideshow purely with images for use in a theatre production (projections). All was fine and I had been fine tuning the presentation however this morning I found that the presentation had dropped an image from the slideshow. Powerpoint will not let me insert this image again and if I try it on a new slide or even a new presentation the image will not insert. I can view the JPEG image on many microsoft programs and I had been viewing it with no problems before this morning. Very frustrating. Any help would be great. Thanks. Kier...

inserting/merging pages
Trying to find out how I can add (2) 5.5W X 8.5H pages on 1 file with (2) 8.5W X 8.5H pages on a different file? All have the same background. I can not insert new page from page 2 that is 8.5X8.5 because it will be the same size and it does not let me make changes in page setup. Hence the reason I created pages 3 & 4 in a new file. ...

Insert a row based on a macro
Is there a way to insert a row based on a formula. For example, If A5=25, I then want to duplicate the existing row and insert it in the line below. See answers in your other post. Pls don't multi post -- Don Guillett SalesAid Software donaldb@281.com "Mindie" <Mindie@discussions.microsoft.com> wrote in message news:75A1DDD5-6334-410D-8646-091B43D1B198@microsoft.com... > Is there a way to insert a row based on a formula. For example, If A5=25, I > then want to duplicate the existing row and insert it in the line below. ...

insert 'x' number of rows in excel
Hi, I'm looking to insert 'x' no. of rows into an excel document where 'x' is the result of a 'countA' function. Is this possible? Hi# only possible with VBA. Would this be feasible for you? >-----Original Message----- >Hi, >I'm looking to insert 'x' no. of rows into an excel document where 'x' is >the result of a 'countA' function. > >Is this possible? >. > Yes VBA is feasible "Frank Kabel" wrote: > Hi# > only possible with VBA. Would this be feasible for you? > > >-----Original ...

Insert row, table to table
Hi, As I cannot use INSERT INTO with values specified to WHERE. I have created an interim temporary table where the record contains two text strings.Now I want to be able to take those text strings and append to the correct table where the record is set by an ID number. First_Name = Fred Last_Name = Jones Its a new contact so it is too be added to a customer with an ID = 1375 Now I can't use the following sql statement strsql = "INSERT INTO [tblCUSTCONTACTS] ( [FIRST_NAME], [LAST_NAME] ) " _ & " VALUES (""" & strFirst & "&...

Adding to a list of codes
Hi In Excel 2003 I have a spreadsheet listing a large number of cost centres. Each month I am given a list of the cost centres and expenditure against them, which I want to use a look up to populate in to a master spreadsheet. However, during the month new cost centres could have been created. What I would like to be able to do is run a macro against the monthly return and compare the codes contained in it to the codes already listed in the master sheet. If there are any new codes not already in the master sheet I would want them added to the existing list so that the mast...

Outlook error codes
I would appreciate any help! Outlook 2007 refuses to startup, error messsage: catastrophic failure (0x8000FFFF) and the following: C:\Users\Matt\AppData\Local\Microsoft\Outlook\ Outlook.pst is not a personal folders file. error code now being displayed: 0x80040119 (is this a registry problem?) the above was the default folder and as such contains a large amount of my inaccessible data Have been unable to activate (or even locate) inbox repair tool (Scanpst.exe), which I understand was installed with Outlook have neutralized spyware uninstalled and reinstalled Outlook 2007, to no ...

bar code help, adding asterix to reference
Hi, A simple one im sure, cell b4 contains a number, i want cell c4 to display the same number with an asterix (asterisk?) at either side for my bar code font? ="*",B4,"*" What's the correct way to enter this? Many thanks as ever, Rick ="*"&A1&"*" seems to work after some trial and error, could there arise a problem doing it this way? Thanks, Rick "R D S" <ricknewsgroup@members.v21.co.uk> wrote in message news:352cf1F4gusjfU1@individual.net... > Hi, > A simple one im sure, cell b4 contains a number, i want cell c4...

which code for the SpellLang in English ?
Hi, I created an Excel file (XP/XL:2000) which is using the check spelling with the code : Range(Cells(19, 2), Cells(50, 9)).CheckSpelling SpellLang:=1033 I would like to know the valid number version for the SpellLang, according to the US language The number 1033 is actually valid for the french language of north America .... Fran´┐Żois I just ran a test using English and the Macro recording feature. It spit this out: ActiveCell.FormulaR1C1 = "spelling something wronge" Range("J6").Select Cells.CheckSpelling SpellLang:=1033 It would appear that 103...

Inserted Controls do not work once Saved
I am creating a form with textbox controls and date control. They work fine until I save the document then the controls seem to disappear. Has anyone had this problem before and what is the solution? I have copied the form and re-created the controls, but once again upon saving the form the controls go away. HELP Which version of Word are you using? Note that if you are using Word 2007, inserting content controls, and then saving in Word 97-2003 format, content controls will be converted to static text. -- Stefan Blom Microsoft Word MVP "LRay67" <...

when inserting a hyperlink the view is shortened with ././ at the beginning
when inserting a hyperink, the view is shortened at the beginning of th path with ../../ and it can not be opened by another user. th hyperlinks are to copies of email from a folder on a deskto -- Message posted from http://www.ExcelForum.com ...

Force relative (unprefixed!) paths in Insert Hyperlink dialog
Googling this question turned up a lot of people with the *opposite* problem, so I'm convinced I must be overlooking something obvious. The issue arises on 2 different PCs, running Excel 2000 and 2003, both with WinXP. I have several hundred files to be hyperlinked from a spreadsheet. When done they're all destined for a CD. So I figure we need all the hyperlink paths to be relative, that is, the address field for each link should be in the form \mystuff.txt and not E:\mystuff.txt (nor any other letter plus :\mystuff.txt).. But no matter what I put in the 'Hyperlink Base&...

Inserting a calculated field in a Pivot Table in Excel 2003
In the past I know I have done this but I can't see the option now to insert a calculated field. Does someone know where this is located? Thanks so much. Disregard. I figured it out. Thanks to anyone who answers before reading this. "Nadine" wrote: > In the past I know I have done this but I can't see the option now to insert > a calculated field. Does someone know where this is located? Thanks so much. ...

Inserting Multiple Worksheets?
Is it possible insert multiple blank worksheets into a workbook? I.E. I want to insert 20 fresh worksheets without having to insert one shee 20 times. Also, supposing I have a set of tabs with each one corresponding to th day of the month, is it possible to somehow drag more worksheets tab that would continue the naming system -- Mr Zer ----------------------------------------------------------------------- Mr Zero's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27724 Hi you can insert...

How list inventory item codes so that they will be sorted correctl
...