Excel Vba Paste Error 438
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
Vba Paste Error 1004
site About Us Learn more about Stack Overflow the company Business Learn excel vba runtime error 438 object doesn't support this property or method more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x
Vba Pastespecial Method Of Range Class Failed
Dismiss 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 VBA excel pastespecial Run-time error 438 appears when “paste” runs up vote 0 down vote favorite I'm just getting familiar with VBA and my code For k = 3 To ThisWorkbook.Sheets.Count ThisWorkbook.Sheets(k).Activate ActiveSheet.Cells(11, 2).Select lLastRow = Cells(Rows.Count, 1).End(xlUp).Row ActiveSheet.Range("A5:" & "A" & CStr(lLastRow)).Copy ' ThisWorkbook.Sheets(1).Cells("B" & CStr(lFirstRow) & ":B" & CStr(lLastRow)).Select ThisWorkbook.Sheets(1).Activate ActiveSheet.Cells(lFirstRow, 2).Select Selection.Paste 'Selection.PasteSpecial Paste:=xlPasteValues 'Selection.PasteSpecial Paste:=xlPasteFormats lFirstRow = lFirstRow + lLastRow Next k makes range paste "Run-time error 438. Object doesn't support this porperty or method" to appear when the line "Selection.Paste" goes. What's the problem?:( I've tried to use paste special, to activate sheet and to select cell (not range), to use Cstr, but nothing changed excel vba excel-vba paste share|improve this question asked Mar 5 '14 at 9:34 Seya 46129 Range object doesn't suppor method Paste. Use ActiveSheet.Range("A5:" & "A" & CStr(lLastRow)).Copy ThisWorkbook.Sheets(1).Cells(lFirstRow, 2) –simoco Mar 5 '14 at 9:59 Try replacing Selection.Paste with Activesheet.Paste –Søren Holten Hansen Mar 5 '14 at 10:02 tnx guys, you are the best –Seya Mar 5 '14 at 10:13 add a comment| 1 Answer 1 active oldest votes up vote 5 down vote accepted Try Selection.PasteSpecial xlPasteAll Paste by itself works on several objects, most notably Worksheet but not on a Range object which is what your Selection is. To paste to a Range you really have to use the PasteSpecial method with its' available arguements such as xlPasteAll; xlPasteValues; xlPasteFormulas; xlPasteFormats and others which you can see by pressing F1 while the cursor is within PasteSpecial in the VBE. share|improve
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies
Run Time Error 438 Object Doesn't Support This Property Or Method
of this site About Us Learn more about Stack Overflow the company run time error 438 vba Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges
Xlpastevalues
Ask Question x Dismiss 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 http://stackoverflow.com/questions/22193723/vba-run-time-error-438-appears-when-paste-runs minute: Sign up Run-time Error '438' 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 http://stackoverflow.com/questions/19402891/run-time-error-438-using-pastespecial openWb As Workbook Set openWb = Workbooks.Open(path) Dim openWs 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("She
Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models http://www.ozgrid.com/forum/showthread.php?t=15714 In Excel | Excel Web Pages | Excel Video Training Forum New Posts FAQ Calendar Forum Actions Mark Forums Read Quick Links Today's Posts What's New? Advanced Search Forum HELP FORUMS Excel General [Solved] VBA: Error 438 when attempting a VBA cut/paste Excel Training / Excel Dashboards Reports If this is your first visit, be sure to check out the FAQ by clicking the link error 438 above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above. Click here to view the relaunched Ozgrid newsletter. Results 1 to 6 of 6 Thread: [Solved] VBA: Error 438 time error 438 when attempting a VBA cut/paste Thread Tools Show Printable Version Search Thread Advanced Search September 7th, 2003 #1 niril View Profile View Forum Posts I agreed to these rules Join Date 11th June 2003 Posts 8 Hey everyone, I'm getting this really weird problem when using VBA to run a cut paste. This is what i'm doing. What it does is the following. I have a list of alphabetically sorted names, side by side. It then compares the name in the side by side cells to each other. If they match, moves on, if they don't match, it cuts everything from one column, and moves it down one. Repeat. In the case where a name appears twice or more in a row, it keeps inserting new lines, until it runs out of duplicate names and moves on. Do x = x + 1 FirstName = Range("a" & x).Value LastName = Range("b" & x).Value LoginName = Range("f" & x).Value compare1 = InStr(1, LoginName, FirstName, 1) compare2 = InStr(1, LoginName, LastName, 1) If (compare1 = 0) Then NextName = Range("f" & x - 1).Value If (NextName = LoginName) Then Range("F" & x + 1 & ":H100").Cut 'Selection.Cut Ra