CODEWITHSUNDEEP
excelvbaexcel-formula
Freedox
Freedox

Reputation: 123

VBA to copy from one workbook and paste to another

JOB: To copy RANGE from one WORKBOOK to ANOTHER (ANOTHER workbook exists and needs to be opened)

  1. Copy range:

    `Worksheets("paste").Range("A2:BD500").SpecialCells(xlCellTypeVisible).Copy`

  2. Open new file:

    Workbooks.Open Filename:="C:\Test\test.xlsx", WriteResPassword:="WriteFile"

  3. Activate sheet and paste @RANGE A6

    Windows("test.xlsx").Activate Selection.Range("A6").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False

PROBLEM: It doesn't paste in A6!!!! It goes in whatever cell!!!!

Upvotes: 4

Views: 15108

Answers (2)

Modi
Modi

Reputation: 1

A simple way to copy a specific range between workbooks:

Workbooks(source).Worksheets("Sheet1").Range("A2:BD500").Copy _
    Workbooks(destination).Worksheets("Sheet1").Range("A6")

Upvotes: 0

user4039065
user4039065

Reputation:

If your current selection in the test.xlsx workbook opens to D5 then using Selection.Range("A6") references D10, not A6.

Dim wb As Workbook

Set wb = Workbooks.Open(Filename:="C:\Test\test.xlsx", WriteResPassword:="WriteFile")

With Worksheets("paste")
    .Range("A2:BD500").SpecialCells(xlCellTypeVisible).Copy
    wb.Worksheets(1).Cells(6, "A").PasteSpecial xlPasteValues
End With

See How to avoid using Select in Excel VBA macros. You should never rely on a static cell or cells being the current selection when opening a workbook.

Upvotes: 6

Related Questions