0

I have to copy over a bunch of data from one workbook to another and I generally use this method:

wkbk1.activate
range(cells(2,col1),cells(lr,col1).select
application.cutcopymode = false
selection.copy
wkbk2.activate
Range("C2").select
activesheet.paste

I have to transfer like 20+ columns of data and they columns don't translate 1:1 to the 2nd workbook, so I can't just copy an entire range. Is there a more efficient way to transfer all this data from one workbook to another?

Jeff
  • 43
  • 1
  • 6
  • 4
    I recommend reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424) – Ike Dec 14 '22 at 14:54
  • Please share an example of *"bunch of data"* (e.g. 3 columns) and explain what *"the columns don't translate 1:1 to the 2nd workbook"* means. For example, copy only values from `wb1.Sheets("Sheet1").Range("A2:ALr")`, `.Range("G2:GLr")` and `.Range("X2:XLr")` where each `Lr` is different to `wb2.Sheets("Sheet1").Range("A2:C2")` would clarify a lot. – VBasic2008 Dec 15 '22 at 10:59
  • @VBasic2008 It's essentially 40+ file from one database and each file is being transferred to another database and the new database has an upload function. The export from the first database does not match up exactly. So column1 from DB1 export is not the same column 1 being uploaded to DB2. If not did, I imagine I could just copy the entire range. – Jeff Dec 20 '22 at 15:47

1 Answers1

0

wkbk1.Range((cells(2,col1),cells(lr,col1)).Copy wkbk2.Range("C2").Paste

It will be faster if you don't use select and activate. wkbk2.range((cells(2,col1),cells(lr,col1)).value = wkbk1.range((cells(2,col1),cells(lr,col1)).value

You also use .value instead of copy-paste

jast
  • 15
  • 4
  • I tried this and I got a not supported error. wbk2.range(cells(2,1),cells(lrf-1,1)).value = wbk2.range(cells(sr+1,2),cells(lrf,2)).value – Jeff Dec 20 '22 at 15:47
  • I'm calling the function that uses the 2 workbooks, and I'm sure I've passed the name correctly, because I can view the locals window and it shows the 2 names and they are labeled as variant/object/thisworkbook – Jeff Dec 20 '22 at 15:54