Posts by Prairie

    This seems to be a reoccurring issue but no straight forward answer

    Trying to send 1 mail merge (email) to a person with multiple records
    obviously don't want them to get multiple letters/emails

    Reading up - many have said use the directory mail merge feature but the rules I have tried do not seem to work [ATTACH]n1221589[/ATTACH]

    Trying to send to each person a list of their outstanding transactions and Total of

    Attached is a sample file

    Many thanks

    I thought the real deal for Power Pivot was using more than 1 sheet to produce pivot tables. In some ways doing away with fiddling with multiple tables in Access to then bring into Excel

    But when I try to use three sheets to combine all fields into a pivot table using Power Pivot I run into the relationship restrictions of not being able to use one to many - have to use unique records

    Does this mean that in order to use these 3 sheets from my sample I can not use Power Pivot ?

    My user wants to combine all 3 sheets displaying all fields from each sheet into 1 sheet -
    All 3 sheets have a school code 4 digit field labeled in Red in each sheet

    Address Sheet = Cost Center 4# field
    School Counts Sheet = 4 Digit field
    Percent Total Sheet = School# field

    I ended up using access to query on 2 sheets and then from that sheet with the 3rd sheet to give her what she needed

    But I am wondering if/how this would be doable using Power Pivot?

    Sample attached
    Thank you

    thank you for quick response
    error message when run on
    if not"combined"
    member or data member not found

    what am I missing?

    I have a workbook with over 128 sheets
    I am tweaking a vba rountine that I thought did work including an additional column of name of worksheet
    I thought it would combine all sheets in a workbook

    can anyone see what is missing/not correct why not all the sheets and rows are being combined ?
    Thank you

    Sub combined()

    Dim J As Integer

    On Error Resume Next



    Sheets(1).Name = "Combined"



    ActiveSheet.Range("A1:A" & Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).Value = ActiveSheet.Name


    Selection.Copy Destination:=Sheets(1).Range("A1")


    For J = 2 To Sheets.Count



    ActiveSheet.Range("A1:A" & Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).Value = ActiveSheet.Name



    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)



    End Sub


    Wanted to follow-up since you have been so helpful
    I was able to find some code to prevent the prompts to continue (and the user having to click through them) when opening the files to copy the row
    the set wb ..................... I have in red seems to be the addition needed to prevent the prompts

    here is the final - and it works like a charm - again my thanks

    almost there!
    so the rows seem to be copying without summing -- perfect - replacing those lines seem do the check but I will double check
    I am still getting update prompts

    I tried coping line after the screen update line
    also before it (not sure it makes a difference)
    as well as without the screen update line
    but still getting prompts or syntax errors
    what I have so far is attached

    Thank you[ATTACH=JSON]{"data-align":"none","data-size":"large","data-attachmentid":1210321}[/ATTACH]

    Don't kill me but I see another issue I did not anticipate
    because the row we are copying is a sum row it is replicating the sum on every cell
    can we just have the row copied without the formula - just as text ?
    Attached sample - each copied cell sums the column not a copy of the value [ATTACH=JSON]{"data-align":"left","data-size":"medium","data-attachmentid":1210303}[/ATTACH] from the original sheet

    Thank you for your time and effort
    Much appreciated

    No option to end or debug
    only when runs message @ links to update or don't update

    I did step through with F8
    Screen shot attached

    the master file is in the same folder as the test files = on desktop in testing folder
    the test files are all excel - but not open -- (she will have 200 so hopefully they do not need to touched at all)
    they all have a sheet called summary with the row range b9.k9 (I sent you a sample)

    But no the code does not seem to do anything

    Many thanks for your time

    it did not flag any line just that error message this is the code with my changes

    We have 200 Excel survey workbooks collected in 1 folder
    Our Director wants to collect all the responses that are recorded in the Summary Sheet of each of the files (there are many other sheets in each of these workbooks)
    Specifically B9:K9 from Summary in each of the 200 files into 1 workbook = 1 sheet = Survey Summary

    I have been playing with some of the VBA file combine codes posted but the ones I am finding combine to one workbook and all the sheets are collected not appended as 1 sheet
    Some of the code is based on workbooks being already open

    I am looking for the code to
    designate where the folder of 200 files resides
    The specific sheet name
    The specific cells in the designated sheet
    a new workbook with 1 sheet of all the 200 rows from each file

    Hopefully I am making sense
    Posted sample of summary sheet

    Thank you

    Thank you for your prompt response - much appreciated

    What is the "Z" referencing in the cell references?
    each of these sheets goes from a1.j200

    Sheets("Combined").Range(Cells(a1, "z"), Cells(j200, "z")) = ws.Name

    I am learning VBA so tend to tweak existing code from other sources so far
    I used some VBA code to combine sheets for any workbook and it works great for any workbook

    what I would like to include in the final combined sheet is an extra column which displays the sheet name of each of the records so I can track where the original data came from
    Here is the code I am using now

    ub CopyRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    Dim ws As Worksheet
    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = "Combined"
    Sheets(2).Rows(1).EntireRow.Copy Sheets("Combined").Cells(1, 1)
    For Each ws In Sheets
    If ws.Name <> "Combined" Then
    LastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ws.Range("A2:Y" & LastRow).Copy Sheets("Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
    Next ws
    Application.ScreenUpdating = True
    End Sub

    How should I edit the code to include a new column in the combined sheet which includes the sheet name ?

    Many thanks