Copy/Paste Alternative - another workbook

[Tr3yAnderson]

Hi ya'll,

I'm trying to figure out how to iteratively transfer rows of data from hundreds of workbooks to one master workbook. I don't want to do copy and paste as it will be too slow and fill up the clipboard and I don't want to mess with the clipboard. I thought I could use the Range("A2") = Range("A2").Value concept. But apparently that doesn't work with different workbooks? My line of code is below, does this really not work across different workbooks or is there something else I'm forgetting? else is there another way to accomplish this without having to do copy and paste? Thanks


Workbooks("Test Master MS Report.xlsm").Sheets("Time").Range(Cells(r, 1), Cells(r, 18)) = Workbooks("Test MS report.xlsx").Sheets("Time").Range(Cells(r, 1), Cells(r, 18)).Value

 

[Fluff]

One way of doing it without looping (which is very slow) would be

Dim Ary As Variant

With Workbooks("Test MS report.xlsx").Sheets("Time")
   Ary = .Range("A2:R" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
End With
Workbooks("Test Master MS Report.xlsm").Sheets("Time").Range("A2").Resize(UBound(Ary), 18).Value = Ary

 

[Tr3yAnderson]

Thanks for the reply. let me give better details into what I'm trying to do. See I'm only wanting a select few rows (non adjacent) within a workbook so I wouldn't be able to grab a large range chunk of data. Basically, I have a master workbook with rows of data. This master workbook was sent throughout the organization and where supervisors (hundreds of them) updated a few rows assigned to them then saved their own workbook with their edits. I now have hundreds of these workbooks I need to combine. A vlookup works well for one time iteration but I couldn't figure out how to do that iterativly. So my next thought was to just loop through the rows of the first workbook and copy the all rows that have had changes and paste that into a blank workbook and do that row by row and workbook by workbook to build out a new master workbook. any ideas how to do this better and quicker than what I have planned? Thanks

 

[Fluff]

How do you know what has changed?

 

[Tr3yAnderson]

How do you know what has changed?

sorry yes, so the master sheet has columns A-M as a database pull and Columns N-R are blank. Supervisors each get a copy of the master file and fill in columns N-R. I didn't see how filtering the data by one of the N-R columns would work as sometimes N will have data inputted and not in the rest or only column R will have been updated and not the rest. so I'm looping through each row and finding which rows have a counta >0 in columns N-R. and if so the plan was to copy the entire row and place that into another a blank workbook and repeat that process for all rows within a worbkook then move on to the next workbook and basically build out a new master workbook that way

 

[Fluff]

How about putting the counta formula in (say col S), then you could filter on that column & then copy all the data in one hit.

 

[Tr3yAnderson]

How about putting the counta formula in (say col S), then you could filter on that column & then copy all the data in one hit.

Ha, it's funny how you can get so deep into figuring out something that you don't see something so easy...yes that would definitely tackle that step for me. I'll do that in tandem with your first suggestion and see how that works out. Thank you much

 

[Fluff]

You won't be able to copy it to an array when it's filtered, but you could use

With Workbooks("Test MS report.xlsx").Sheets("Time")
  .Range("A2:R" & .Range("A" & Rows.Count).End(xlUp).Row).Copy Workbooks("Test Master MS Report.xlsm").Sheets("Time").Range("A2")
End With