Pasting into another workbook

**mattmc419** · 10-21-2014, 11:41 PM

Hey Guys,

I'm trying to write a macro that copies values from one sheet and pastes them into Sheet1 in a different workbook.

I have the code below, but it errors out at the pasting step ( Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone )

The error is:

"Run-time error '1004';

PasteSpecial method of Range Class failed"

Anyone have any idea what's going on?

Thanks!

Sub InventorySave()

Dim wbk As Workbook

Sheet4.Range("A1:G1000").Copy

Set wbk = Workbooks.Open("C:\Users\Matt.Matt-Laptop\Desktop\InventoryTemplate.xlsm")

With wbk.Sheets("Sheet1")

Range("A1:G1000").ClearContents

Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:="C:\Users\Matt.Matt-Laptop\Desktop\InventoryTemplate.xlsm", FileFormat:=52

    ActiveWorkbook.SaveAs Filename:="C:\Users\Matt.Matt-Laptop\Desktop\Inventory Upload.txt", FileFormat:=xlTextWindows

Application.DisplayAlerts = True

ActiveWorkbook.Close False

End With

End Sub

**Vikas_Gautam** · 10-21-2014, 11:57 PM

The Problem is that you used clearcontents method before pastespecial method..
The possible correction would be....

Sub InventorySave()

OrgFile=Activeworkbook.name

Dim wbk As Workbook

Set wbk = Workbooks.Open("C:\Users\Matt.Matt-Laptop\Desktop\InventoryTemplate.xlsm")

With wbk.Sheets("Sheet1")

Range("A1:G1000").ClearContents

Workbooks(orgfile).sheets("sheet4").range("A1:G1000").copy
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:="C:\Users\Matt.Matt-Laptop\Desktop\InventoryTemplate.xlsm", FileFormat:=52

    ActiveWorkbook.SaveAs Filename:="C:\Users\Matt.Matt-Laptop\Desktop\Inventory Upload.txt", FileFormat:=xlTextWindows

Application.DisplayAlerts = True

ActiveWorkbook.Close False

End With

End Sub

**mattmc419** · 10-22-2014, 11:21 AM

hmm now I'm getting,

"Run-time error '9':

Subscript out of range"

With debugging telling me that error is at,

Workbooks(OrgFile).Sheets("sheet4").Range("A1:G1000").Copy

**Vikas_Gautam** · 10-22-2014, 12:28 PM

Try this then..
Or Upload both files to work upon...

Sub InventorySave()
OrgFile=Activeworkbook.name
Dim wbk As Workbook
Set wbk = Workbooks.Open("C:\Users\Matt.Matt-Laptop\Desktop\InventoryTemplate.xlsm")
With wbk.Sheets("Sheet1")
Range("A1:G1000").ClearContents
Workbooks(orgfile).activate
sheets("sheet4").range("A1:G1000").copy
Workbooks(wbk.name).activate
Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:="C:\Users\Matt.Matt-Laptop\Desktop\InventoryTemplate.xlsm", FileFormat:=52
    ActiveWorkbook.SaveAs Filename:="C:\Users\Matt.Matt-Laptop\Desktop\Inventory Upload.txt", FileFormat:=xlTextWindows
Application.DisplayAlerts = True
ActiveWorkbook.Close False
End With
End Sub

Pasting into another workbook

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Pasting into another workbook

Re: Pasting into another workbook

Re: Pasting into another workbook

Re: Pasting into another workbook

Thread Information

Users Browsing this Thread

Similar Threads

macro to find matching value in workbook and pasting it to another workbook

[SOLVED] Opening new workbook and pasting values into current workbook - code issues

Filtering in another workbook, copying filtered data and pasting in source workbook

[SOLVED] Macro Wanted for looping thru worksheets in one workbook and pasting to another workbook!!

Excel 2007 : Copy Data from Sheets in one Workbook and pasting it into another Workbook Sheet

Bookmarks

Bookmarks

Posting Permissions