Excel Vba Runtime Error 438 Pasting
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site excel vba runtime error 438 combobox About Us Learn more about Stack Overflow the company Business Learn more
Excel Vba Runtime Error 438 Object Doesn't Support
about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss
Excel Vba Runtime Error 438 Object Doesn't Support This Property Or Method
Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Run-time Error '438'
Run Time Error 438 Excel Vba
using .PasteSpecial up vote 1 down vote favorite I am trying to create a simple Macro to copy data from a closed Excel file into the current one that I have open. So far I have created this Sub CopyData() Dim path As String path = "C:\Users\sam\Coding\bk.xlsx" Dim currentWb As Workbook Set currentWb = ThisWorkbook Dim openWb As Workbook Set openWb = Workbooks.Open(path) Dim openWs run time error 438 vba excel 2010 As Worksheet Set openWs = openWb.Sheets("Sheet1") currentWb.Activate openWb.Activate openWs.Range("A1:C2").Copy currentWb.Range("A1").PasteSpecial openWb.Close (False) End Sub But I get a RunTime Error 438 and upon debug it highlights the row "currentWb.Range("A1").PasteSpecial". I have search all over the place to find an answer but I haven't been successful. My question is, what am I missing? Thank you in advance! vba excel-vba share|improve this question edited Mar 2 '15 at 21:37 pnuts 33.8k63769 asked Oct 16 '13 at 12:02 sammojohn 4517 add a comment| 1 Answer 1 active oldest votes up vote 1 down vote accepted The problem is currentWb.Range("A1").PasteSpecial It should be currentWb.Sheets("SomeSheet").Range("A1").PasteSpecial xlPasteAll replace xlPasteAll with whatever you are trying. Range object is not a part of Workbook but of Worksheet Also you don't need to use .Activate. You code can be written as Sub CopyData() Dim path As String Dim currentWb As Workbook, openWb As Workbook Dim currentWs As Worksheet, openWs As Worksheet path = "C:\Users\sam\Coding\bk.xlsx" Set currentWb = ThisWorkbook '~~> Change this applicable Set currentWs = currentWb.Sheets("Sheet1") Set openWb = Workbooks.Open(path) Set openWs = openWb.Sheets("Sheet1") openWs.Range("A1:C2").Copy currentWs.Range("A1").PasteSpecial xlPasteValues openWb.Close (False) End Sub share|improve this answer answered Oct 16 '13 at 12:27 Siddharth
here for a quick overview of the site Help Center Detailed answers to any questions you might have vba paste error 1004 Meta Discuss the workings and policies of this site About Us vba pastespecial method of range class failed Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with excel pastespecial us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just http://stackoverflow.com/questions/19402891/run-time-error-438-using-pastespecial like you, helping each other. Join them; it only takes a minute: Sign up Excel Macro giving error when pasting up vote 1 down vote favorite I am trying to create an excel macro which is probably going to end up being quite large, to make things easier I am tackling it a bit at a http://stackoverflow.com/questions/8702086/excel-macro-giving-error-when-pasting time. So far I have.... Sub Macro4() ' ' Test Macro ' 'Selects the product_name column by header name Dim rngAddress As Range Set rngAddress = Range("A1:Z1").Find("product_name") If rngAddress Is Nothing Then MsgBox "The product_name column was not found." Exit Sub End If Range(rngAddress, rngAddress.End(xlDown)).Select 'Inserts new column to the left of the product_name column Selection.Insert Shift:=xlToRight 'Re-selects the product_name column Range(rngAddress, rngAddress.End(xlDown)).Select 'Copys the contents of the product_name column Selection.Copy Selection.Paste End Sub I want it to do the following.... Search the spreadsheet for the header name 'product_name' Insert a blank column to the left of the 'product_name' column Copy the contents of the 'product_name' column Paste them into the newly created blank column Change the header name in this new column to 'product_name_2' Currently it works fine up until the pasting into this newly created column, then i get a 'Run-time error '438'; - Object doesn't support this property or method' Can anyone suggest where i am going wrong? excel vba excel-vba share|improve
Forum Microsoft Office Application Help - Excel Help forum Excel Programming / VBA / Macros [SOLVED] [SOLVED] Object doesn't support this property or method To get replies http://www.excelforum.com/showthread.php?t=369643 by our experts at nominal charges, follow this link to buy points and post your thread in our Commercial Services forum! Here is the FAQ for this forum. Closed Thread Results http://www.teachexcel.com/excel-help/excel-how-to.php?i=536012 1 to 13 of 13 [SOLVED] Object doesn't support this property or method Thread Tools Show Printable Version Subscribe to this Thread… Mark this thread as unsolved… Display Linear Mode error 438 Switch to Hybrid Mode Switch to Threaded Mode 05-10-2005,02:06 PM #1 davegb Guest [SOLVED] Object doesn't support this property or method I'm trying to paste some text from one worksheet to another, but I keep getting the error "Object doesn't support this property or method". I checked in the object browser and it shows range, offset and paste as belonging time error 438 to worksheets. I'm trying to paste the contents of the clipboard into cells starting 2 rows below the last cell with data in column a on the "County Records" worksheet. Worksheets("County Records").Range("a5").xlDown.Offset(2, 0).Paste I know some of you will spot this in a second. Please let me know what I'm doing wrong, and why this doesn't work even though the object browser tells me they all "fit". Thanks in advance. Register To Reply 05-10-2005,03:06 PM #2 Jim Thomlinson Guest RE: Object doesn't support this property or method xlDown is an argument of the .end property. This should work Worksheets("County Records").Range("a5").End(xlDown).Offset(2, 0).Paste HTH "davegb" wrote: > I'm trying to paste some text from one worksheet to another, but I keep > getting the error "Object doesn't support this property or method". I > checked in the object browser and it shows range, offset and paste as > belonging to worksheets. > I'm trying to paste the contents of the clipboard into cells starting 2 > rows below the last cell with data in column a on the "County Records" > work
from one .xls file into another. For some reason, however, the Selection.Paste function gives it a problem (Error 438: Object doesnt support this property or method). Heres the code...Im trying to copy the cells from "weektest.xls" to "emailer.xls", running the macro in emailer.xls Workbooks.Open Filename:="weektest.xls" Cells.Select Selection.Copy Windows("emailer.xls").Activate Cells.Select Selection.Paste Workbooks("weektest.xls").Close SaveChanges:=True any ideas? Free Excel Help Forum - Ask any question about Excel and have it answered in no time. Similar Excel Tutorials Quickly Move Cells Around a worksheet in Excel Cut/Paste Trick Moving Cells around a worksheet in Excel is fast an easy. Below I have a table of forenames, surnames and ages. As ... How to View the Selection Pane in Excel When there are No Visible Objects The Selection Pane is a very useful tool in Excel that allows you to view all of the objects, shapes, charts, pictu ... Error bars in Charts in Excel How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ... Prevent Errors From Appearing in Excel How to prevent errors from appearing in formulas in Excel. This is especially helpful for the Vlookup function. Sec ... Helpful Excel Macros Save the Current Worksheet as a New Excel Workbook File - This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a Save the Current Worksheet as a New File in the Current Folder - This Excel Macro saves the currently visible worksheet into the SAME folder as the current file. It is different from Replace Formulas with Values (For The Entire Workbook) - This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to Close a Workbook after a Time Limit is Reached - This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai Excel Macro to Save a Specific Worksheet as a New File - This Excel Macro allows you to