CODEWITHSUNDEEP
excelvba
user1612986
user1612986

Reputation: 1415

copying a range from one workbook to another

can someone please help me with this issue:

i have a macro in workbook1. what i do is

'open workbook2.txt

Workbooks.OpenText filename:= _
    "workbook2.txt", Origin:=437, StartRow _
    :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
    Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
    Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1)), _
    TrailingMinusNumbers:=True

' copy a range
Dim test As Range
Set test= Range("G971:M1000")

'close workbook2
 Workbooks("workbook2").Close

'copy test in workbook1
 Windows("workbook1.xlsm").Activate
Sheets("Sheet1").Select
Range("B3").Select
Selection.PasteSpecial Paste:=test, Operation:=xlNone, SkipBlanks _
     :=False, Transpose:=False

And this results in a runtime error 1004.

It would be very kind of someone to help.

Upvotes: 0

Views: 812

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

You have to copy the range first to paste it. What you need is

test.Copy after the line Set test= Range("G971:M1000")

Also regarding Paste:=test. That is incorrect.

The Paste is an optional parameter which takes any of these values. I would recommend having a look at Excel's help on PasteSpecial

xlPasteAll
xlPasteAllExceptBorders
xlPasteAllMergingConditionalFormats
xlPasteAllUsingSourceTheme
xlPasteColumnWidths
xlPasteComments
xlPasteFormats
xlPasteFormulas
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

Use this

bSource.Sheets("Sheet1").Range("B3").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Upvotes: 2

Related Questions