Copying a specific range in one excel workbook to another

Question

First time poster and I have looked at other responese for the issue, but I need verification of the code.So I have this code below, im pretty sure it is correct but the destination range is not clearing the contents. What I'm trying to do is copy the data from Range ("A:G") in the active workbook to Range("A:G") in a different workbook. But i need to clear the contents on the destination range becauese I need only the most updated source range in the destination range. The commented sections are code I will use once I know the contents are cleared on the destination range.

 Sub CopytoRISKreport()

   Dim Dest_file_name As String
   Dim dest_wkbk As Workbook

   Dest_file_name = "Q:\Co Risk Report\3 Month RISK Report.xlsm"

   Set dest_wkbk = Workbooks.Open(Dest_file_name)
   Application.DisplayAlerts = False

   With dest_wkbk
   Sheets("CO_HOLDS Import").Range("A1:G" & .Rows.Count).ClearContents

   'Workbooks("Q:\Co Risk Report\CO Holds.xls").Select
   'Selection.Worksheets("CO_HOLDS Import").Range("A:G").Copy
   'dest_wkbk.Sheets("CO_HOLDS Import").Range("A:G").PasteSpecial Paste:=xlPasteValues
   End With 

End Sub

`Sheets("CO_HOLDS Import").Range("A:G").ClearContents` and [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, Apr 24 '14 at 21:16
I have done it without the selection format and still does not work. — moreira.rene.r, Apr 24 '14 at 21:19
how exactly it doesn't work? use `.Sheets("CO_HOLDS Import").Range()` instead `Sheets("CO_HOLDS Import").Range()` — Dmitry Pavliv, Apr 24 '14 at 21:20
The destination workbook opens, selects the tab with "CO_Holds Import" tab, but does not clear the contents of the range I specify. Its as if the code just stops at the Sheet. I made the changes but still same issue. — moreira.rene.r, Apr 24 '14 at 21:22

Rachel Hettinger · Accepted Answer · 2014-04-24T22:35:48.573

0

You asked for verification of the code... Here's a cleaned up version. Since you are pasting over the entire columns A:G, you do not need to clear them first. Also, you can paste as part of the copy method; this saves a line of code.

Sub CopytoRISKreport()

    Const Dest_file_name As String = "Q:\Co Risk Report\3 Month RISK Report.xlsm"
    Const Source_file_name As String = "Q:\Co Risk Report\CO Holds.xls"

    Dim dest_wkbk As Workbook
    Dim Dest As Range
    Dim source_wkbk As Workbook
    Dim Source As Range

    Set dest_wkbk = Workbooks.Open(Dest_file_name)
    Set Dest = dest_wkbk.Worksheets("CO_HOLDS Import").Range("A:G")

    Set source_wkbk = Workbooks.Open(Source_file_name)
    Set Source = source_wkbk.Worksheets("CO_HOLDS Import").Range("A:G")

    Source.Copy Dest

End Sub

Note: If you turn off Alerts, be sure to turn them back on later.

UPDATE: Modified code so that the source file is opened.

edited Apr 24 '14 at 22:35

answered Apr 24 '14 at 21:38

Rachel Hettinger

7,927
2
21
31

Thanks for the clean up and reply. But still does not work. After I run the code destination file open and takes me to the destination tab but does nothing. Maybe there is an issue with excel not the code itself. – moreira.rene.r Apr 24 '14 at 21:50
I updated the code so that the source file is opened via code. If the macro is in the source file, you can modify this to `Set source_wkbk = ThisWorkbook`. – Rachel Hettinger Apr 24 '14 at 22:05
Thanks Rachel. Unfortunatley it still is not working even with the mods. It runs into the same issue. Opens destination folder but does not copy contents. – moreira.rene.r Apr 24 '14 at 22:18
@moreira.rene.r There was a space before the Source_file_name which I just removed. Be sure to have both the source and the destination workbooks closed before you run the code. – Rachel Hettinger Apr 24 '14 at 22:38

Copying a specific range in one excel workbook to another

1 Answers1