CODEWITHSUNDEEP
excelvbamultiple-columnscopy-paste
Boomer
Boomer

Reputation: 239

using copy destination vba between workbooks

Been working a project using recorded macros to complete copying and pasting between workbooks. Recorded macros has been working BUT, when reading the forums people are stating copy/paste method takes more time for macro to run and is very inefficient. So I'm trying to sound out a copy destination vba line that would copy several columns starting from Range(A2:G2000) and then paste to range area starting at (B6:H2000). I'm not good at this and trying to learn more efficiency. Two things here come to mind. A simple copy and paste method which I have working. BUT would utilize the straight source = destination. Could this be done? After reading this you may see another issue. Why have down to Row 2000 selected and copy/pasted? You are correct in your thinking. Some reports(txt files) have 100 rows, some 300, none more than 1000 but I'm overdoing the copy selection because I'm won't be sure about future txt files. I was thinking if you could just select the column down to the last row used would be cool. This is way beyond me. I'd be happy just a new way to copy/paste efficiently.
I would appreciate any help that anyone could offer. let me know what you think. Thanks Boomer

enter code here
   Sub import_data()
   '
   '
    'import_data
    '
    Application.ScreenUpdating = False
    '
   'Opens the txt file in excel - text delimited and leaves file open until 
   we close at bottom of code. There is no        'name for this workbook.

    Workbooks.OpenText (Module33.FileDir + "\cf_data.txt"), Origin:=437, _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
    , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), 
      _
     Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), 
   TrailingMinusNumbers:=True


  'The line below is where I'm selecting data from the current opened 
  workbook and copying to another workbook

   ActiveWindow.Sheet1.Range("A2:G2000").Copy 
    Destination:=Workbooks("Auto_Data.xlsm").Sheet2.Range       ("B6:H2000")



   'This info below is a (recorded marco).  All works if I rem out the above 
    line and open up all the below lines

    '    Range("A2:G2000").Select
    '    Selection.Copy
    '    Windows("Auto_Data.xlsm").Activate
    '    Sheet2.Select
   '    Range("B6:H6").Select
    '    ActiveSheet.Paste
    '    Selection.AutoFilter
    '    Application.CutCopyMode = False
    '    ActiveWindow.ActivateNext
    '    ActiveWindow.Close
    '    Range("B4").Select
    Application.ScreenUpdating = True

   End Sub

Upvotes: 0

Views: 1105

Answers (1)

user11138753
user11138753

Reputation:

A direct value transfer is typically faster than a copy and uses less memory since it doesn't involve the clipboard.

Sub import_data()

    dim lr as long, arr as variant
    dim wb1 as workbook

    set wb1 = Workbooks.OpenText(filename:=Module33.FileDir & "\cf_data.txt"), Origin:=437, _
                                 StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
                                 ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
                                 Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
                                 Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), 
                                 TrailingMinusNumbers:=True)

    with wb1.sheets(1)

        lr = .range("A:G").Find(what:="*", after:=.range("A1"), searchorder:=xlbyrows, _
                                searchdirection:=xlprevious).row
        .range(.cells(2, "A"), .cells(lr, "G")).value

    end with

    wb1.close

    Workbooks("Auto_Data.xlsm").Sheet2.Range("B6").resize(ubound(arr, 1), ubound(arr, 2)) = arr

end sub

Upvotes: 1

Related Questions