VBA to copy from one workbook and paste to another

4
votes

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!!!!

2
excelvbaexcel-formula
post your code as it is in your workbook. Very hard to Discern what the issue is from bullet points....... – Sorceri

2 Answers

5
votes

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.

0
votes

A simple way to copy a specific range between workbooks:

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