From: Nit_Wit_400 on
This is a new issue but with the same project.. I don't mean to side
track those of you helping me....

As I mentioned earlier, there will be a pivot table created from a
large spreadsheet given to us from an outside company.

Unfortunately, those spreadsheets are hardly ever the same, so I'm
trying to implement a way to make a pivot table no matter what is
given to me.

I have the following code so far.... (pasted from notepad)

Public Sub CreatePivotTable ()
Dim SheetRange As String
Dim FirstColHeader As String

FirstColHeader = 'Name of First Column Header' <- Unsure how to do

Selection.Insert Shift:=xlDown
Sheetrange = 'Name of Imported Sheet & Selected Range' <- Unsure how
to do this
ActiveWordbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=(SheetRange).CreatePivotTable _
TableDestination:= "Sheet2", TableName:="PivotTable1", _
Default Version:=xlPivotTableVersion10
With ActiveSheet
.PivotTableWizard TableDestination:=ActiveCells(3,1)
End With
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTalbes("PivotTable1").PivotTableFields
.Orientation = xlPageField
.Position = 1
End With
......... <---- formatting the table once it's made.

End Sub

Public Sub Rows()
Dim CurrentCol As Str
Dim ColCount As Long
Dim i As Long
i = 1
ColCount = 'Number of Active Columns' <----------- Unsure how to do

For i to ColCount
CurrentCol = 'Name of Current Column + 1'
With ActiveSheet.PivotTables("PivotTable1").PivotFields(CurrentCol)
.Orientation = xlRowField
.Position = i
End With
Next i
End Sub

Was wondering if I was headed on the right track here...

From: joel on

to get the number of columns

Assuming row 1 has data to the last column
ColCount = cells(1,columns.count).end(xltoleft).column

joel's Profile: 229
View this thread:

[url=&quot;;]Microsoft Office Help[/url]

From: macropod on
Hi Nit_Wit_400,

> Plus, I've run into another curve-ball... I want to be able to merge hyperlinked text as well
That's not a problem.
If you mean that you want a hyperlink to appear, simply insert the relevant mergefield into a HYPERLINK field in Word.
If you mean that the mergefield contains the path to a file (eg a text file or a Word file) that you want to insert, simply insert
the relevant mergefield into an INCLUDETEXT field in Word - if it's a picture, use an INCLUDEPICTURE field instead.

[Microsoft MVP - Word]

"Nit_Wit_400" <kflinspach(a)> wrote in message news:754bff8c-5a21-4800-af2a-2dae238bd61d(a)
> Thanks macropod,
> You're always very helpful... I think in this case though, my data is
> too complicated...
> Plus, I've run into another curve-ball... I want to be able to merge
> hyperlinked text as well... otherwise, converting to .txt and then
> merging would work beautifully.. thanks for that tip too, joel.
> Joel,
> Would converting to html keep hypertext?
> Maybe I should give you all the complete details of the table I'm
> trying to merge since this discussion is getting far more in-depth
> than I'd anticipated.
> On my "Report" sheet, I have a pivot table set up to show one row at a
> time of a VERY large spreadsheet we get from an outside company. Next
> to the pivot table I have 10 macro buttons set up to paste 10
> different kind of "Whitesheets" (that are each contained within the
> workbook on hidden sheets) which are basically checklists that are run
> on each row of the LARGE spreadsheet checking for defects in the
> record... there are 10 different kinds of records hence 10 different
> whitesheets.
> Each whitesheet is different, buy only slightly... they have the same
> idea:
> A cell for reference to the record being checked (example: book/page
> number), a cell for the person filling out the whitesheet, a cell for
> the type of record being analyzed and then a table with a column for:
> "Item - which indicates each part of the record - a column for "check"
> - which indicates whether the item was checked - a column for "defect"
> - which indicates if the item is defected - a column for "notes" -
> which are the analyst's notes - and a column for "standard" - which
> indicates our standard remedy for each defect (hyperlinked to a Word
> document)
> As the analyst goes through each record, and fills out a whitesheet,
> they're going to hit another button contained in each whitesheet
> called "Report" which will do .... something.... and then they can go
> on to the next record and a new whitesheet, which is being pasted over
> the existing one to make it easier to read and so that the analysts
> can work in the same page.
> The result I want is some sort of report showing each defect in each
> whitesheet with each standard so that it can easily be sent off to the
> next person who makes comment on the report.
> So far, my "Report" button goes through the table and deletes the rows
> within the whitesheet for which the "defect" column's cells are
> blank. The next step would be figuring out where to put the table so
> it can be viewed along with the (possibly hundreds of) other tables.
> My original vision was that I'd be able to directly paste that table
> into Word with the macro... but that's proving to be far beyond me, so
> then I thought about using the mailmerge function.... and that too is
> turning out to be pretty complicated. The best result would be for
> the report to be in a Word document, but if that can't be done, I can
> possibly work something else out.
> Thank you all so much for your tips!
> Sorry for racking your brains!