Bom pessoal, peguei um código aqui no fórum para copiar dados de planilha fechada.
Meu objetivo é o seguinte.
Tenho 10 workbook com dados, cada uma tem 3 worksheets. São todas pastas-tipo, ou seja, todas iguais, a não serem os dados inseridos, que dependem de outros fatores. Enfim, o que eu quero, é copiar os dados de uma região que chamei de PARCIAIS, que estão localizadas em uma worksheet chamada Dados HH e colar num workbook que ira agregar todos os dados.

Os 10 workbooks possuem nomes diferentes, relativos às suas funções, no workbook geral, estão dispostas linhas que irão conter esses valores, então, além desse código para copiar os dados, eu precisaria de um pra inserir os dados, mas que utilize a função lookup para inserir no local correto.

Não estou conseguindo desenvolver isso na minha cabeça.

Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
    FolderName = "D:testing"
    ' create list of workbooks in foldername' --- Comment
    wbCount = 0
    wbName = Dir(FolderName & "" & "*.xls")""
    While wbName <> ""
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = wbName
        wbName = Dir
    Wend
    If wbCount = 0 Then Exit Sub
    ' get values from each workbook' --- Comment
    r = 0
    Workbooks.Add
    For i = 1 To wbCount
        r = r + 1
        cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1", "A1")
        Cells(r, 1).Formula = wbList(i)
        Cells(r, 2).Formula = cValue
    Next i
End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
    wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
    GetInfoFromClosedFile = ""
    If Right(wbPath, 1) <> "" Then wbPath = wbPath & ""
    If Dir(wbPath & "" & wbName) = "" Then Exit Function
    arg = "'" &amp; wbPath &amp; "[" &amp; wbName &amp; "]" &amp; _                              <--------------
        wsName &amp; "'!" &amp; Range(cellRef).Address(True, True, xlR1C1)                        <--------------
    On Error Resume Next
    GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

as linhas que contém "<--------------" estão retornando um erro '1004': O método 'Range' do objeto '_Global' falhou.