Inputbox To prompt Start of Data Range

Hi All,

I have a code which lets me merge sheets courtesy a fellow forum member.

The below snippet of the code merges the sheets in a given workbook in case data in each sheet starts from row 1 and cell a1.

I modify it as per the scenario....if the data starts in row 3 then I modify Rows(1).copy to Rows(3).copy and [a1].CurrentRegion to [a3].CurrentRegion

Is it possible to have an inputbox which lets the user decide where the data starts by providing these inputs without having to modify the code?

Thanks!

With .Sheets(1)
            For i = 2 To Sheets.Count
                Sheets(i).Rows(1).Copy .Rows(1)
                lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
                Sheets(i).[a1].CurrentRegion.Offset(1).Copy .Cells(lRow, 1)
            Next

Re: Inputbox To prompt Start of Data Range

Thanks Alan!

I am getting a Run Time error '424': Object Required....below line is highlighted

Sheets(i).[a & x].CurrentRegion.Offset(1).Copy .Cells(lRow, 1)

Thanks.

Re: Inputbox To prompt Start of Data Range

Thanks Pike,

Below is complete code in its existing form.

What this does is:
1)Prompts the user to select the target file from which sheets are to be merged.
2)Combines/merge all data sheets into one in a new workbook with tab name as today's date and time.
3)Everyday/everytime this macro is run a new worksheet with today's date and time as name will get populated alongside yesterday's sheet.

So nothing is copied as such because there won't be existing data.

Sub ImportData() 
    Dim wb As Workbook, lRow As Long, i As Integer, sPath As String 
     
    Application.ScreenUpdating = False 
    With Application.FileDialog(msoFileDialogOpen) 
        .AllowMultiSelect = False 
        i = .Show: If i = 0 Then Exit Sub 
        sPath = .SelectedItems(1) 
    End With 
    Set wb = Workbooks.Open(sPath) 
    With wb 
         '// Add temporary sheet for combined Data
        Sheets.Add .Sheets(1) 
         '// Add headers and combine relevant rows to new sheet
        With .Sheets(1) 
            For i = 2 To Sheets.Count 
                Sheets(i).Rows(7).Copy .Rows(1) 
                lRow = Cells(Rows.Count, 1).End(xlUp).Row + 1 
                Sheets(i).[a7].CurrentRegion.Offset(1).Copy .Cells(lRow, 1) 
            Next 
             '// Move the temporary sheet to this workbook
            ThisWorkbook.Activate 
            .Move After:=ThisWorkbook.Sheets(Sheets.Count) 
            wb.Close False '// Close the selected file without saving temporary sheet
        End With 
    End With 
     '// Name the new sheet with date and time of creation
    Sheets(Sheets.Count).Name = Format(Now, "mm-dd-yyyy_hh.nn") 
    Application.ScreenUpdating = True 
     
End Sub

Re: Inputbox To prompt Start of Data Range

Hi Pike,

If I make the change you suggested, only the 7th row from each sheet is copied.

Attaching examples. forum.ozgrid.com/index.php?attachment/66537/ sheet is the one from where I execute the macro from, while forum.ozgrid.com/index.php?attachment/66538/ is my sample data from which sheets are to be merged.

In this example, data to be copied starts from row 7.

Thanks.

Re: Inputbox To prompt Start of Data Range

Sorry if I have ended confusing you. What I meant was that the contiguous data set may start from any row(in the scenario above its the 7th row).

The code in its original form above works successfully if the data to be merged from each sheet starts from the 7th row and the entire current region is copied.

However there maybe times where the data will start from the 3rd or 4th row or for that matter any row. So I want to try and provide the user the option to provide that as a variable rather than amend the code for each scenario.

So basically I want all contigious(current region) content in the sheets to be copied. My intent is not copy just the row specified.

Thanks