Posts by andyb16

    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.


    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. sheet is the one from where I execute the macro from, while is my sample data from which sheets are to be merged.

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


    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.

    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?


    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)

    Re: Need Help with My Existing Code to merge sheets

    Thanks KjBox!!

    This worked and I was able to figure out much of the code :)

    Just one the sample workbook there were 3 reality there maybe several with varying column numbers everyday (though number of columns and headers will always be consistent and in same order). Is there anyway to account for that?


    Hi All,

    The attached sheet has my sample data - .

    I was able to build code to merge data in all sheets into a single sheet (all headers in all sheets are consistent). Data to be merged with headers starts on row7.

    My aim is (and I am struggling here) is to run this code from a different workbook say "MergedSheets" which will

    • Prompt the user to select the target file from which sheets are to be merged.
    • Combine/merge all data sheets into one but this combined result should get added as a new worksheet in the "MergedSheets" worksheet with tab name as today's date.
    • Everyday/everytime this macro is run a new worksheet with today's date as name should get populated alongside yesterday's sheet.

    My current code is as below:

    Re: Pivot Table - Conditional Format rows Depending on Latest Date

    Hi Holycow,

    Thanks for your response.

    Attached is my sample pivot

    By objective is that the row with latest month under every pillar should get highlighted.

    For example, in the attached sample all rows with Oct month should get highlighted.


    Re: Formula for Running YTD Average

    Hi SO and Batman,

    First of all apologies for the late response and thank you for your suggestions.

    I am getting the desired results from the above formulas.

    HI Batman - thanks for tip - "(3) Your YTD cost is based on 'cost per person per month', not just 'cost per month', otherwise you would be basing your calculations on column D (salary total) instead." You were right. I had to do some changes to my core data.


    Hi All,

    My basic objective is to look up the cost per month of each project in the previous months and print the average YTD cost in that month in Col F.

    For example Project A cost per month in April is 10. In May if it is 20, then in the next column of YTD average, April Cost will be same since it is the first month i.e 10. But in May it will be 15 ((10+20)/2).

    Please refer attached xls for reference

    I tried to use nested ifs with vlookups but not getting anywhere.

    Please note there are 50+ projects across 12 months in my data, hence simple average formula that I have use for the example wont work.