data from multiple files: best way to extract

[Hankjam]

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

 

[lhkittle]

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