data from multiple files: best way to extract
Hello
I have a question relating to data extraction. I have a number of files, which are records of subpots. Sometimes I would like to extract data from each of them, lets say Cells A1, C3, C5, C7 and C11. The way I have done this in the past is probably bonkers.... on the top line have cells with =A1, =C3, =C5, =C7 and =C11, opened each file, copied this range into each and then copied and paste special into a new sheet.... it works but takes a bunch of time.... so there must be a better way... having the file name in a cell, which is then read by the recieving cells. I surely hope I have explained my query. Many thanks for reading this far. Hj |
data from multiple files: best way to extract
On Sunday, April 21, 2013 1:01:17 AM UTC-7, Hankjam wrote:
Hello I have a question relating to data extraction. I have a number of files, which are records of subpots. Sometimes I would like to extract data from each of them, lets say Cells A1, C3, C5, C7 and C11. The way I have done this in the past is probably bonkers.... on the top line have cells with =A1, =C3, =C5, =C7 and =C11, opened each file, copied this range into each and then copied and paste special into a new sheet.... it works but takes a bunch of time.... so there must be a better way... having the file name in a cell, which is then read by the recieving cells. I surely hope I have explained my query. Many thanks for reading this far. Hj Hi Hj, Try this, where you will select the cells you want to transfer to another sheet to column F. Select cells one at a time, if you select say three in a row together they will get pasted as three in a row on new sheet. Option Explicit Sub Copy_Selected_Cells() Dim sCells As Range On Error GoTo 0 For Each sCells In Selection.Areas ' Copy to same sheet 'sCells.Copy Range("F100").End(xlUp).Offset(1, 0) ' Copy to another sheet, amend sheet name... Copy Sheets("AnotherSheet") sCells.Copy Sheets("Sheet3").Range("F100").End(xlUp).Offset(1, 0) Next End Sub Regards, Howards |
All times are GMT +1. The time now is 11:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com