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="http://www.thecodecage.com"]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 |