Adding data into consecutive excel sheets with VBA

Question

I am trying to copy data from external workbooks titled Field_1, Field_2, ..., all the way up to Field_10 without having to open them first. I want to add this data into sheets 2 all the way up to sheet 11 of my main workbook. I already have code that successfully adds new empty sheets and renames them to Field_1 and so on (ie. Sheet2 becomes Field_1). The problem I am having is copying the data into each of the sheets.

So far, I've successfully written code to retrieve the data and I can make all the data get pasted into just Sheet2 (as shown in the code below the first line of asterisks) but the data overrides each other on each loop. Chaning the sheetID each loop doesn't paste anything into any of the cells. I have included my entire code incase useful. The important parts are encompassed in the asterisks.

Sub ADDCLM7()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
Dim Lookup_Row As Long
Dim Lookup_Clm As Long
Dim wbPath As String, wbName As String
Dim wsName As String, cellRef As String
Dim Ret As String
Dim FilePath$, Row&, Column&, Address$

Range("AA2").Activate
i = 1
While i < 11
    Table1 = Sheet1.Range("AB2:AB28471")

    fileTitle = "Field_"
    fileTitle = fileTitle & CStr(i) & ".xls"
    SheetName = "Sheet1"
    NumRows = 77 
    NumColumns = 2
    FilePath = ActiveWorkbook.Path & "\"

    DoEvents
    Application.ScreenUpdating = False
    If Dir(FilePath & fileTitle) = Empty Then
        MsgBox "The file " & fileTitle & " was not found", , "File Doesn't Exist"
        Exit Sub
    End If
    For Row = 1 To NumRows
        For Column = 1 To NumColumns
            Address = Cells(Row, Column).Address
'*************************************************************************************************
             Sheet2.Cells(Row, Column) = GetData(FilePath, fileTitle, SheetName, Address) ' THIS ONLY PASTES INTO SHEET2

            'sheetID = "Sheet"
            'sheetID = sheetID & CStr(i)
            'sheetID.Cells(Row, Column) = GetData(FilePath, fileTitle, SheetName, Address) ' THIS DOESNT PASTE ANYTHING
'*************************************************************************************************
        Next Column
    Next Row
    ActiveWindow.DisplayZeros = False

Table2 = Sheet2.Range("A2:B77")
Dept_Row = Sheet1.Range("AB2").Row 
Dept_Clm = Sheet1.Range("AB2").Column
Lookup_Row = Sheet1.Range("AA2").Row
Lookup_Clm = Sheet1.Range("AA2").Column

i = i + 1
For Each cl In Table1
    lookupCode = Sheet1.Cells(Lookup_Row, Lookup_Clm).Value
    Sheet1.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(lookupCode, Table2, 2, False)
  Dept_Row = Dept_Row + 1
  Lookup_Row = Lookup_Row + 1
Next cl
Wend
End Sub

Any help is greatly appreciated.

Have you tried using the index of the sheets ? Like Sheets(1) or Sheets(2) or in your case Sheets(i + 1), if i = 1 to begin with and you want data in sheet 2. — Vulthil
– Vulthil, Commented Jan 13, 2016 at 7:02

Gareth · Accepted Answer · 2016-01-13 08:32:54Z

1

It's because SheetId is a string not a worksheet in your loop and therefore does not have a method or property Cells.

Change this line:

sheetID.Cells(Row, Column) = GetData(FilePath, fileTitle, SheetName, Address)

To:

ThisWorkbook.Worksheets(sheetID).Cells(Row, Column) = GetData(FilePath, fileTitle, SheetName, Address)

As a side note, I'd suggest adding Option Explicit to the very top of your code and declare all of your variables which will help in future avoid issues like this.

edited Jan 13, 2016 at 8:32

answered Jan 13, 2016 at 8:15

Gareth

5,2636 gold badges47 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding data into consecutive excel sheets with VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related