CODEWITHSUNDEEP
vbaexcel
K Pekosh
K Pekosh

Reputation: 693

Range declarations to copy from one workbook and paste to another

I want to copy values from one workbook and paste them into a master workbook.

The line 

Set DestRange = DIAAggregation.Range(1 & NRow)

stops the debugger and gives me the error message:

Method 'Range' of object "_workbook" failed

Upon looking online, I am aware that I am not fully qualifying my range, but I do not see what I can do to fully qualify it.

The code is below, and the line in question is the last line.

Sub DIA_Concatenate()
    '
    '

    '
    '

    Dim DIAAggregation As Worksheet
    Dim DIAMaster As Workbook
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range

    Dim Month As String
    Dim Year As String

    ' Prompts the user to specify which DIA data
    ' is being aggregated (Month and Year).
    ' Useful for checking data source and SaveAs file name.
    Month = InputBox("What month's data is being aggregated?")
    Year = InputBox("What year's data is being aggregated?")

    ' Points the macro to the proper data source
    ' (UPDATE THIS LINE TO YOUR DATA SOURCE!!!)
    FolderPath = _
        "G:\Analytical Services\General Team Folders\Kyle\DIA Aggregation Bank\"

    ' Opens the master workbook that is to have data added to it,
    ' and declares the first sheet for the macro.
    Set DIAMaster = Workbooks.Open(FolderPath & "*Aggregation*")
    Set DIAAggregation = DIAMaster.Worksheets(1)

    ' Incrementer to keep track of where new rows should be appended.
    NRow = DIAAggregation.Rows.Count + 1

    Dim LastRow As Long

    ' Call Dir the first time, 
    ' pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until all .xl files in the source folder have been read.

    Do While FileName <> ""
        If InStr(1, FileName, "Aggregation") > 0 Then
            FileName = Dir()
            GoTo Jump
        End If

        If InStr(1, FileName, Month) = 0 Then
            FileName = Dir()
            GoTo Jump
        End If

        ' Open a workbook in the folder.

        Set WorkBk = Workbooks.Open(FolderPath & FileName)

        Dim J As Integer

        ' Loop through data sheets to collect data.

        For J = 2 To Sheets.Count ' From sheet 2 to last sheet.
            ' Make the sheet active, find where the data is,
            ' and select the data.
            Sheets(J).Activate 

            LastRow = WorkBk.Worksheets(J).Cells.Find(What:="*", _
              After:=WorkBk.Worksheets(J).Cells.Range("A1"), _
              SearchDirection:=xlPrevious, _
              LookIn:=xlFormulas, _
              SearchOrder:=xlByRows).Row

            Set SourceRange = WorkBk.Worksheets(J).Range("A3:E" & LastRow)

            ' Set the destination range to start at column A and
            ' be the same size as the source range.

            Set DestRange = DIAAggregation.Range(1 & NRow)

Upvotes: 0

Views: 44

Answers (1)

K Pekosh
K Pekosh

Reputation: 693

Per the last comment, changing the declaration of NRow by adding in .UsedRange between the spreadsheet variable and .Rows resolves the issue PartyHatPanda pointed out.

Upvotes: 1

Related Questions