Prev: Userform problem with "Run Time Error 75"
Next: ExportAsFixedFormat : differences between Excel and Word
From: Nit_Wit_400 on 30 Dec 2009 15:36
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
this

Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A4").Select
Selection.CurrentRegion.Select
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)
.Cells(3,1).Select
End With
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTalbes("PivotTable1").PivotTableFields
(FirstColHeader)
.Orientation = xlPageField
.Position = 1
End With
Rows
......... <---- 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
this

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

Thanks!
From: joel on 30 Dec 2009 17:24

to get the number of columns


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


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165640

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

From: macropod on 30 Dec 2009 18:41
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.

--
Cheers
macropod
[Microsoft MVP - Word]


"Nit_Wit_400" <kflinspach(a)gmail.com> wrote in message news:754bff8c-5a21-4800-af2a-2dae238bd61d(a)t12g2000vbk.googlegroups.com...
> 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!

First  |  Prev  | 
Pages: 1 2 3
Prev: Userform problem with "Run Time Error 75"
Next: ExportAsFixedFormat : differences between Excel and Word