Pasting into an active cell

I have some VBA code (see below) where I am copying a fixed selection from 
one workbook (which the user has selected), into the next empty row in a 
different workbook (where the macro is located).


Sheets("National").Select
Range("A6:X21").Select
Selection.Copy

Windows("Diagnostics 2010-2011.xls").Activate
Sheets("C-National").Select
Selection.paste

However, I get a run time error (438) on the paste command when I try to run 
it.

Any suggestions, as I am new to VBA
0
Utf
3/1/2010 11:39:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1321 Views

Similar Articles

[PageSpeed] 34

Try the below which will copy the range from the Activeworkbook Sheet 
National to openworkbook Diagnostics Sheet c-national...

Sub Macro()
Dim wb As Workbook

Set wb = Workbooks("Diagnostics 2010-2011.xls")
Sheets("National").Range("A6:X21").Copy _
wb.Sheets("C-National").Range("A1")
End Sub



-- 
Jacob


"BoRed79" wrote:

> I have some VBA code (see below) where I am copying a fixed selection from 
> one workbook (which the user has selected), into the next empty row in a 
> different workbook (where the macro is located).
> 
> 
> Sheets("National").Select
> Range("A6:X21").Select
> Selection.Copy
> 
> Windows("Diagnostics 2010-2011.xls").Activate
> Sheets("C-National").Select
> Selection.paste
> 
> However, I get a run time error (438) on the paste command when I try to run 
> it.
> 
> Any suggestions, as I am new to VBA
0
Utf
3/1/2010 11:56:01 AM
To Activecell

Sub Macro()
Dim wb As Workbook

Set wb = Workbooks("Diagnostics 2010-2011.xls")
Sheets("National").Range("A6:X21").Copy _
wb.Sheets("C-National").ActiveCell
End Sub


-- 
Jacob


"BoRed79" wrote:

> I have some VBA code (see below) where I am copying a fixed selection from 
> one workbook (which the user has selected), into the next empty row in a 
> different workbook (where the macro is located).
> 
> 
> Sheets("National").Select
> Range("A6:X21").Select
> Selection.Copy
> 
> Windows("Diagnostics 2010-2011.xls").Activate
> Sheets("C-National").Select
> Selection.paste
> 
> However, I get a run time error (438) on the paste command when I try to run 
> it.
> 
> Any suggestions, as I am new to VBA
0
Utf
3/1/2010 12:01:01 PM
Hi,

Try this

Sheets("National").Range("A6:X21").Copy _
    Destination:=Workbooks("Diagnostics 
2010-2011.xlsx").Sheets("C-National").Range("A1")
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"BoRed79" wrote:

> I have some VBA code (see below) where I am copying a fixed selection from 
> one workbook (which the user has selected), into the next empty row in a 
> different workbook (where the macro is located).
> 
> 
> Sheets("National").Select
> Range("A6:X21").Select
> Selection.Copy
> 
> Windows("Diagnostics 2010-2011.xls").Activate
> Sheets("C-National").Select
> Selection.paste
> 
> However, I get a run time error (438) on the paste command when I try to run 
> it.
> 
> Any suggestions, as I am new to VBA
0
Utf
3/1/2010 12:06:01 PM
Reply:

Similar Artilces:

Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but whenever I try to copy from one file to another, my paste special function treats the copied cells as a picture. Instead of the normal paste special popup, where it asks me how I want the data to come out, I get a different popup that shows the source as a "Microsoft Office Excel Worksheet Object." It asks me what format I want the picture to come out as, and gives me a list of different file formats. If I try to just paste a link, it puts a picture of the other file on the new one. I talked to so...

Active X Control in Outlook
I have a user who is using Outlook XP and when he goes to send an email he gets the following error. Your current security settings prohibit running Active X Control on this page. As a result, the page may not display correctly. The title bar of this window says Microsoft Internet Explorer. I tried disabling under the custom button in I.E. his Active X controls but no go. Where is the setting to disable this restriction. The user has some images or graphics on his emails that I believe is causing this error. Thanks. Sounds like a default signature to me. Tried recreating the (HTM...

Will excel 2003 format a cell to the year?
It seems like it should be obvious, but I am finding it impossible to get excel to do a date format that recognizes a year. Is there some way? I typed a date in A1 (09/28/2006) and used format|Cells|number tab|custom category|yyyy with my USA settings. kateofmd@msn.com wrote: > > It seems like it should be obvious, but I am finding it impossible to > get excel to do a date format that recognizes a year. Is there some > way? -- Dave Peterson ...

Conditional format
Hi Conditional format Is there a way to get a cell's format determined by the format of a different cell?! e.g. Could one get an entire row market up in say bold red if one cell in that row was say less than 100 With thanks Ship Shiperton Henethe Hi Shiperton yep ) select the rows that you want to apply the conditional formatting to, ensuring that the first row of this group is the first row at the top of the screen (it gets confused sometimes if it isn't) the following example assums you've selected from row 2 onwards and you want the row to turn red if ...

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

Can't activate trial version #2
I ordered a trial version and I've tried 3 times to activate it and I keep getting the error message that I used the wrong product key to install it. I double checked it 3 times. HELP! ...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

all cells in 2 workbooks have automaticlly changed to date format
Why would excel 2007 automaticly change formats? Upon opening a workbook this morning all the blank cells in the 16 sheets had changed from general format to date format, and 99.9% of cells with data or formulas were also changed to the date format. I opened another workbook and it was find, I closed it and reopened it and the same thing occurred, most cells had been changed to the date format. The first wb containts payroll data, with mostly sumifs functions and was working fine yesturday. The second wb contains data using vlookup and if functions. Thank you Deb Found the...

Money Plus Activation #3
I purchased Money Plus Deluxe from Amazon.com, and can't activate due to our corporate firewall. The software refers me to digitalriver and a phone number, however, neither of them will assist me because I did not purchase & download it from them. Both refer me to the support.microsoft.com/money website. At the website, I call the number for support, am granted a case number, and upon speaking with Money support, am told that they do not do activations. The software says that it can be activated by calling in with the product key. However, I can't seem to reach anyone that ...

Number of activations:
Hi! Is it possible to see how many times a Home and Student version has been activated? Thanks, Yes, you count the machines that it currently installed on, and working. dsh wrote: > Hi! > > Is it possible to see how many times a Home and Student version has been > activated? > > Thanks, Why worry, you can activate as many times as necessary, you just cannot run it on more than the number of working machines allowed in the EULA, usually 3 "dsh" <dsh@discussions.microsoft.com> wrote in message news:F3619AEB-8A87-456C-A5CF-381...

Activity Views 11-15-04
I would like to add "Date Started" to the Activities view for each opportunity. Is there a way to configure the Activity Views for Opportunities to include specific fields? On the CRM Server open a web browser and copy the required link below. You will be able to modify the view in the same way as other views are modified. Replace "CRMSERVER" with the name of your crm server. Guy. Associated View: Activities: http://crmserver/tools/viewEditor/viewManager.aspx?id={00000000-0000-0000-00AA-000010001903} View: All Activities: http://crmserver/tools/viewEditor/viewManage...

Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells me it is in the clipboard; however, when I go to paste that information into another document, Publisher acts like it is not there. I then need to close the document I wish to paste it into, reopen it, and then, like magic, there is the information in the clipboard and I am able to paste it. UGH!!!!!! -- Ginger Christenson Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program are you pasting to? If it is an Office program, open the Office Clipboard in that program too. -- Mary Sauer ...

Pasting formulas advances cell reference
Hi When i paste special from one cell into another, it advances the cell reference and i just want an exact match i.e. A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes it as ='Feb 2010'!D18 The C changes to D, how do i stop this happening? Thanks for any help in advance Derek Use absolute referencing. Below are the different reference styles. A1 Relative referencing. Both column and row will change if you copy or drag the formula. $A1 The column reference is fixed and will not change A$1 The row reference is fixed and wil...

Copy/Paste loop
I have a form on one sheet in excel, on the next sheet i have mad different coloums with different headings. I want the data from th form to be copyed to each heading e.g. name in form is copyed to unde the coloum with title name. So each time a user fills in there details he/she clicks submit an there data is copyed to the next page, like a small database. Is there a macro that can do this as i keep getting different error each time. I want this to continue in a loop Please help!!:confused -- Message posted from http://www.ExcelForum.com This might get you started: Option Explicit Opti...

Keep certain cells from moving
I'm sure this will be an easy one, but I can't seem to find the info i help. I'm using Excel 97 and have a simple sheet with a few columns o data. I've setup auto-filters for colums A-E. What I want to to is put some formulas in column F that don't ge blanked out when I use the filters (if they're on a line that doesn' meet the condition I've selected, they dissappear). I'd also like the to give me dynamic totals. For instance, I have dollar amounts attributed to certain people in on column and their names in another. If I use the filter to select o...

Cannot release cell
As soon as I open Excel and click into the first cell, the program will not release the cell. It will not even let me access the Menus above. Is this a software issue? I believe there is a quick keyboard sequence fix but I dont know what it is and can not find the information anywhere (including MS Excel TroubleShoot KnowledgeBase) Do you see EXT in the statusbar (bottom right corner)? If yes, hit the F8 key to toggle this off. Have you tried a different mouse or mouse driver? MEvans wrote: > > As soon as I open Excel and click into the first cell, the program will not > ...

Activity Tracking Clean-up
Hi There, Many people are asking about how to track users activity in the system, and some have warned that this could fill up the DB quickly (not really sure about this...). But, no one has ever brought up the question about how to clean-up that activity once it's over and done. For example, I've seen companies were users activity tracking was enable for months or years, and nobody was really aware about it. I've searched thru the GP documentation and found nothing about the clean-up process, nor is there any remove/cleanup option in the maintenance. The activity tracking ...

When I copy a picture and paste it into Publisher it disappears
I have tried to copy a picture from my documents and paste it into Publisher and the picture disappears. What version of Publisher? What happens if you go to... Insert / Picture / From File? -- John Inzer "Cathy" <Cathy@discussions.microsoft.com> wrote in message news:EF2CA50C-78D9-47DB-8799-E9C3C9171038@microsoft.com... >I have tried to copy a picture from my documents and paste it into >Publisher > and the picture disappears. I am using Office Profession Edition 2003. I tried another picture and so far it is still there. Thanks. "John Inzer"...

need to Copy or Move to active cell from specified range
I may be the last person remaining on earth who still uses the original Lotus 1-2-3 Rel 1A, which I know and love, on an antique DOS machine. I've never had a reason to upgrade to a later version of 1-2-3, and have no idea how it now behaves, but one important feature of Release 1A is that instead of rigidly assuming that Copy or Move will always refer to the contents of the active cell, /c (or /m) followed by Backspace frees the cell pointer without changing the active cell. This means that you can easily perform a "reverse" Copy or Move, by going to the cell you want copied, a...

enter a current date in a cell?
How can I enter a formula in a cell so that when I click on that cell it will show today's date or the current date? You can only get excel to do stuff when you click by using a worksheet change event. If you want to enter the current time or date quickly, try Ctrl+; or Ctrl+Shift+: -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "Tpeyton77" <Tpeyton77@discussions.microsoft.com> wrote in message news:A6FD36E5-16A5-42C2-B0BA-CB72580199BD@microsoft.com... > How can I enter a formula in a cell so that when I cli...

How do I have an active cell highlight automatically
I am working in excel and having trouble seeing the active cell. I would like to have the active cell highlighted. Is this possible? It would make it possible to find the active cell easier. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 5 End With With .Bo...

How to count merged cells
Can someone help me in developing a formula that will: 1. Count how many merged cells in a column that are created from 2 cells AND contain both text and numbers? 2. Count how many merged cells in a column that are created from 3 cells AND contain both text and numbers? 3. Council how may merged cells in a column that are blank. If you look in the archives of this group you will see countless examples of reasons not to use merged cells. To concatenate cells is fine, to merge is asking for trouble. -- David Biddulph "Excel Ella" <ExcelElla@discussions.microsof...

Incrementing a cell selection
How can I increment a cell selection in a formula? For example, I would like to count a group of cells, 30 at a time, going down a column. ie if i have this formula: '=count(L2:L31)' and I want to skip down to the end of this range and count the next 30 cells, how do I do it? I know that this will not work '=count(L2+30:L31+30)' Any assistance is much appreciated, thanks, On Fri, 30 Mar 2012 07:27:51 GMT, Mil Browne wrote in microsoft.public.excel: >How can I increment a cell selection in a formula? For example, I would like to count a group of cells, 30 at a time, g...

Field TO of an Activity at blank
Hi, May I configure the TO into a E-mail Activity where ever I am in CRM. ex: I am in a contact I create an Activity I would like the field TO � blank Thanks, Sylvie There is no supported way to customize Activity objects. Sorry and Good luck! >-----Original Message----- >Hi, > >May I configure the TO into a E-mail Activity where ever=20 I am in CRM. > >ex: I am in a contact >I create an Activity >I would like the field TO =E0 blank > >Thanks, > >Sylvie > > >. > ...

Paste and Paste Special
What are the differences between paste and paste special and are these two functions the same for XP software from different sources? e.g Word, Publisher, Ventura and Word Perfect? -- _ _________________________________________ / \._._ |_ _ _ /' Orpheus Internet Services \_/| |_)| |(/_|_|_> / 'Internet for Everyone' _______ | ___________./ http://www.orpheusinternet.co.uk ...