I have a spreadsheet with 3 pivot tables. Each of these tables has a different data source coming from 3 different spreadsheets. All saved in the G: drive of the network. Every now and then, I get an error re Cannot open PivotTable source file. The "G:" part of the source has disappeared just leaving the rest of the reference. Any idea why this is happening (happens maybe once every couple of weeks)
Posts by tac_79
-
-
Update...have put pivot table and source data in separate workbooks and now very quick...any way to keep them in the same workbook though?
-
I have a Pivot Table that is about 25000 rows of data by 10 columns. I have only ever done much smaller data sets. Each change to this larger pivot table takes 30-35 seconds. Is this normal? I deleted the data from the range I did not need and changed the table to about 6000 rows but it did not make much of a difference. TIA
-
-
I have a spreadsheet to collate the time off requests for my staff for their roster. In one cell they need to select 'Date of Request'. I have a drop down list that only allows them to select today's date as defined by a reference cell with =TODAY().
Looking around some forums though, it would look like when I check this tomorrow, the date will have updated.
I want to limit the cell value so that it has to be today's date (so they can't change dates or enter an earlier date to make it look like they were in first) with a static value...
Any help is greatly appreciated!
Cheers
Tom
-
Re: VBA Code to copy worksheets/workbooks into one new workbook
*Update*
Tried the code from post started by gani below...same result...let me copy 3 workbooks before same error appearing
-
I have tried a few VBA codes to either combine 12 workbooks into one (all with 6 worksheets so new workbook would have 76 worksheets) or also to create 6 new workbooks all with the same sheet from the 12 workbooks (all 12 x Sheet1 in one workbook, all 12 x Sheet2 in another). I would prefer the first option. I have tried the below option and had limited success. I get a run-time error 1004 'That name is already taken. Try a different one'.
All worksheets within the individual workbooks have the same name.
Using this code, seems to let me combine 3 workbooks before the error appears
_________________________________________________________
Code
Display MoreSub CombineFiles() Dim Path As String Dim FileName As String Dim Wkb As Workbook Dim WS As Worksheet Application.EnableEvents = False Application.ScreenUpdating = False Path = "O:\Nth Adelaide Manager\Wage Reports\Test" FileName = Dir(Path & "\*.xls", vbNormal) Do Until FileName = "" Set Wkb = Workbooks.Open(FileName:=Path & "" & FileName) For Each WS In Wkb.Worksheets WS.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) Next WS Wkb.Close False FileName = Dir() Loop Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Moderator Comment: Please read the rules you agreed to when you joined this forum. Pay particular attention to the rule on Code Tags. I have added them for you today. Please comply in the future.
-
Re: A quicker way than INDEX to count unique values in a list
Don't I feel like the fool...never knew that existed! Thanks so much!
-
Hi all,
I am using the formula =INDEX($A$2:$A$5800, MATCH(0, COUNTIF(L1:$L$1, $A$2:$A$5800)+IF(COUNTIF($A$2:$A$5800, $A$2:$A$5800)>1, 0, 1), 0)) to create a list of any duplicate values in the range A2:A5800.
This takes a really long time for excel to process...is there an easier (quicker) way? I played with a pivot table which I am new to, but couldn't work out how to filter only if a value was a duplicate.
Cheers,
Tom
-
Re: Sum non-consecutive/contiguous cells by Month and Year
Great...will have a look later today. Thanks all
-
Re: Sum non-consecutive/contiguous cells by Month and Year
Great...thanks very much for your help. I am self taught and don't know much (anything) about pivot tables. I see them mentioned a lot in forums though. I think I have some homework to do!
Cheers
-
I have a spreadsheet with 3 repeated rows - Quantity, Total Price, Unit Price. Next to Quantity is a cell with the date. In my example A10 = Date, C10 = Quantity, C11 = Total Price, C12 = Unit Price. I am looking for a formula to sum all Quantity values. For the sake of trying to get the formula right, I have been using two sets of data A10:C15...but this will go on without end.
I can get an array formula to sum by month ={SUM(IF(MONTH(A10:A15)=11,C10:C15,""))}
I can get an array formula to sum by year {=SUM(IF(YEAR(A10:A15)=2016,C10:C15,""))}I cannot get one to sum by both...I have tried, without success ={SUM(IF(AND(MONTH(A10:A15)=11,YEAR(A10:A15)=2016),C10:C15,""))}
Any ideas? Thanks in advance.
(also...my first post...hopefully I met all the rules/criteria for a good post!)
TC