Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to extract data from multiple Excel files | Excel Programming | |||
Extract cell data from multiple files in one folder | Excel Discussion (Misc queries) | |||
Extract Data from Multiple Excel Files | Excel Discussion (Misc queries) | |||
extract key words/data from multiple files -dump in new worksheet | New Users to Excel | |||
Extract Data From Multiple Excel Files - One File | Excel Programming |