Posts by andyb16

Re: USD30 : Merge worksheets, add formulas and create pivots

10% payment to Ozgrid done

Re: USD30 : Merge worksheets, add formulas and create pivots

Thanks SMC for the revert and taking this up. Will make 10% payment to Ozgrid shortly.

Re: USD30 : Merge worksheets, add formulas and create pivots

Email sent. Will make 10% paypal payment to Ozgrid once you confirm. Tks.

Hello All,

I want to create an automation around:

1) Merging 2 sheets from 2 workbooks
2) Add predefined formulas (vlookups)
3) Create pivots

I will email the dummy files and provide further details once someone wishes to have a look.

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

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

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

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.

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: Need Help with My Existing Code to merge sheets

Thanks!!

Re: Need Help with My Existing Code to merge sheets

Thanks You!!

This was really helpful.

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 request...in the sample workbook there were 3 columns...in 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?

Thanks.

Hi All,

The attached sheet has my sample data - forum.ozgrid.com/index.php?attachment/65866/ .

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:

Sub ShtCombine()    Dim ws As Worksheet
    'Add Top Sheet or Pasting Combined Data
     Worksheets.Add().Name = "All_Sheets_Data"
     'Add Headings in first Row of top Sheet
     Sheets(2).Cells(7, 1).EntireRow.Copy
     Sheets(1).Cells(1, 1).PasteSpecial
          
    'Combine relevant rows to the mastersheet
    For Each ws In ActiveWorkbook.Worksheets
                If ws.Name <> ActiveSheet.Name Then
                    ws.UsedRange.Offset(7).Copy
                        With Range("A65536").End(xlUp).Offset(1, 0)
                            .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                            .PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
                            False, Transpose:=False
                            Application.CutCopyMode = False
                            Range("A1").Select
                        End With
                End If
            Next
End Sub

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

This helps immensely!

Thanks a ton !!! One more new trick learnt courtesy ozgrid.

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

Thanks holycow!

Its working...but when I expand a pivot field and collapse again the row highlight goes away...:(

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

Hi Holycow,

Thanks for your response.

Attached is my sample pivot forum.ozgrid.com/index.php?attachment/62598/.

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.

Thanks!

Hi All,

In my pivot Table, I have dates in Col A.

Is it possible to highlight the entire row corresponding to the latest date?

I tried $A14 = MAX(DATE(2014,8,1),DATE(2014,9,1),DATE(2014,10,1),DATE(2014,11,1),DATE(2014,12,1),DATE(2015,1,1),DATE(2015,2,1),DATE(2015,3,1)).

But not working.

Thanks.

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.

Thanks!

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 forum.ozgrid.com/index.php?attachment/62559/.

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.

Thanks!

Re: Macro to delimit and create column headers

Thanks snb!