copying data ranges

[jaypatel]

Hi

I have 25 differently named spreadsheets located in c: my documents

but, within these 25 spreadsheets will be data range name labelled "Pay1".

how can i extract all these 25 "pay1" ranges into one sheet, but listed below each other?

cheers

Jay

 

[jeffreybrown]

Hi Jay,

Maybe this will help

http://msdn.microsoft.com/en-us/lib...ultipleWorkbooks_MergingRangefromAllWorkbooks

 

[Tuelor]

I would list all the filepaths in column A, then run the following code:

Sub ExtractData()
    Dim xlWb As Excel.Workbook
    Dim iCount As Integer
    Set xlWb = ActiveWorkbook
    For iCount = 1 To 25
        Workbooks.Open xlWb.Sheets(1).Cells(iCount, 1).Value
        xlWb.Sheets(1).Cells(iCount, 2).Value = ActiveWorkbook.Sheets(1).Range("Pay1").Value
        ActiveWorkbook.Close savechanges:=False
    Next iCount
End Sub

D

 

[jaypatel]

Hi

Thanks for all your help.....

The code does work, but the code only copies the first sheet of data:

' Change this range to fit your own needs.
With mybook.Worksheets(1)
Set sourceRange = .Range("A1:C1")
End With

what i am hoping is for choosing range as "pay4"..... i have attempted to change the range as "pay4", but it does not work.

can anyone help?

cheers

Jay