4

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

trek
  • 49
  • 1
  • 1
  • 13
Freedox
  • 123
  • 2
  • 4
  • 11
  • 1
    post your code as it is in your workbook. Very hard to Discern what the issue is from bullet points....... – Sorceri Mar 14 '17 at 17:32

2 Answers2

6

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.

Community
  • 1
  • 1
  • This helped a lot!!! It works on a test sheet but actual sheet is more complex and I'm getting "pastespecial method of range class failed 1004" any idea how to by pass that? – Freedox Mar 15 '17 at 08:58
  • Error is dispalyed @ wb.Worksheets(1).Cells(6, "A").PasteSpecial Paste:=xlPasteValuesAndNumberFormats – Freedox Mar 15 '17 at 08:58
0

A simple way to copy a specific range between workbooks:

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