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

Many Thanks - works like a charm - thank you for your assistance and taking the time
Enjoy the rest of your day!

A have a state report that has a number of rows that are merged with wrapped data.
Worksheet came in as a table which I converted to range.
.
I am trying to unmerge the row and have the wrapped data appear in each row with duplicate column info.

Sample attached
Any assistance appreciated
Thank you

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

So sorry to be a pill still not working -

Quote from graha_karya

Code

Sub test()
dim x,i&
With sheets.add
.name = "Combined"
for i = 1 to sheets.count
[U]if not sheets.name ="Combined" [/U]then
.cells(rows.count,1).end(3)(2).resize(.sheets(i).usedrange.specialcells(11).row-1).value = Sheets(i).[a1].Currentregion.offset(1).resize(.sheets(i).usedrange.specialcells(11).row-1).value
end if
next i
end with
end sub

Display More

thank you for quick response
error message when run on
if not sheets.name="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).Select

Worksheets.Add

Sheets(1).Name = "Combined"

Sheets(2).Activate

ActiveSheet.Range("A1").EntireColumn.Insert

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

Range("A1").EntireRow.Select

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

ActiveSheet.Columns("A").EntireColumn.Delete

For J = 2 To Sheets.Count

Sheets(J).Activate

ActiveSheet.Range("A1").EntireColumn.Insert

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

Range("A1").Select

Selection.CurrentRegion.Select

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

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

ActiveSheet.Columns("A").EntireColumn.Delete

Next

End Sub

StephenR

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

Sub LoopThroughFolder()


Dim oFSO As Object, sPath As String, wb As Workbook


Set oFSO = CreateObject("Scripting.FileSystemObject")


sPath = Environ("userprofile") & "\Desktop\Testing" 'path


For Each ofile In oFSO.GetFolder(sPath).Files
[B][COLOR=#FF0000]   Set wb = Workbooks.Open(ofile, 0) [/COLOR][/B]
   Application.AskToUpdateLinks = False
   wb.Sheets("Summary").Range("B9:K9").Copy
   ThisWorkbook.Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlValues
   wb.Close False
Next ofile


End Sub

Thank you
will investigate - I can certainly work around it

Again - appreciative of your time and effort
if I solve it will let you know
Cheers
Leslie

almost there!
so the rows seem to be copying without summing -- perfect - replacing those lines seem do the check but I will double check
However
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
Leslie

that did the trick!

Last question - the script prompts to update each file
in the real folder there will be 200 files so
is there a way to bypass the prompts and have it automatically update ?

Thank you
Leslie

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
Leslie

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

Sub LoopThroughFolder()


Dim oFSO As Object, sPath As String, wb As Workbook


Set oFSO = CreateObject("Scripting.FileSystemObject")


sPath = Environ("userprofile") & "\Desktop\Testing" 'path


Application.ScreenUpdating = False


For Each oFile In oFSO.GetFolder(sPath).Files
Set wb = Workbooks.Open(oFile)
wb.Sheets("Summary").Range("B9:K9").Copy _
ThisWorkbook.Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)
wb.Close False
Next oFile


Application.ScreenUpdating = True


End Sub

Thank you for your assistance
I tried your code with changes to the path as well as sheet name

However I get a runtime error 91

Object variable or with block variable not set

Is there other lines that needed my custom entry?

Thank you

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
TO
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

got it!
Many thanks - have a wonderful weekend

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

Because Word is such a pain mail merging groups / lists I created Pivot tables for each of our 70 elem schools
Is there any way (VBA?) I can quickly email (Outlook 2016) each sheet to the principal's email (in Pivot) without having to manually email each sheet?

Any Ideas appreciated
Thank you