then i be dead by the time i link all the cells
experts pls come in !
thank U
then i be dead by the time i link all the cells
experts pls come in !
thank U
all the cells are general in default ;;)
really no idea how to make automation
possible to link data to pivot table instead
of click one cell after another.
nope,it still uses reference "=$E$206"
no getpivotdata formula appears.
ahh,i crack my brains on working a for loop
in the end it uses reference cell method.
****
i have this code
For x = 5 To 21 Step 1
colnum = CStr(colnum)
ActiveCell.Value = "=R" + rownum + "C" + colnum
colnum = colnum + 1
Cells(temp, x).Select
Next
End Sub
part of my for loop code,it links the table but getpivotdata formula does not appear.
i guess because i wrote the activecell.value="R1C1" doesn't auto become getpivotdata formula although generate pivotdata is turn on .
i try the code
Cells(1, 1).Select
ActiveCell.Value = "="
Cells(2, 2).Select
but it doesn't link,it just fill in value and leave the cell inactive.because i wanted to write a for loop so that it can use the equal sign and do the getpivotdata formula for me...
like linking A1 from A10
to
B1 to B10
the col A will contain getpivotdata formula assuming col B is a pibot table.
help !!!
I gotten it work,wanna share to others that this piece of code is useful if u want to make a range to horizontal cells linking to another horizontal cells,it can work to user input so gonna add in input box.
Sub Macro1()
'
Dim Source As Integer
Range("A1").Select
MyInt = "1"
For x = 1 To 4 Step 1
haha = CStr(MyInt)
ActiveCell.Value = "=R8C" + haha
Cells(1, x).Select
MyInt = CInt(haha)
MyInt = MyInt + 1
Next
'
End Sub
Sub Macro1()'
Dim Source As Integer
Range("A1").Select
For x = 1 To 4 Step 1
ActiveCell.Value = "=" 'what's here ?!
Cells(1, x).Select
Next
End Sub
i wanted to make some automation for my report,to make A1 = A8 ,B2=B8
get the values of 1 and 8 using input boxes but i set aside first.
but the activecell.value i cannot figure out
what it shld be,it need to change from A to B then to C,a for loop for alphabet ?!
and i gonna be careful about data mismatch too.Help !:barf:
instead of the usual
range("a1").select
i like to use cell reference.
but
Range("R1C1").Select
Range(R1C1).Select
cannot work,what is the method behind it ?
thanks for the quick help
but the
GETPIVOTDATA(" Jan-03",$A$154,"XCVR Source Site","TJ","Region","EMEA","Tech Catg","CDMA")
i tried putting + 31 inside Jan-03 and outside Jan-03 but cannot work...the moment i type 31 and press enter it become #REF! or #VALUE,i keep on trying for the moment now.
oops,my hands itchy again
the help on this error (a small box ) beside the cell when it display =#REF!
disappear after i checking on something,
how to turn it on back ???
cos i wanted to find a way such as when =#REF! it display "0" in the cell
thanks it works...but my formula is rather complicated.
=GETPIVOTDATA(" Jan-03",$A$154,"XCVR Source Site","TJ","Region","EMEA","Tech Catg","CDMA")
The Jan-03 will need to change to Feb-03 on the next cell,that's why i'm think to writing macro to link it first quickly.
Using dragging method does not work in this case.
thanks anyway.any solutions ?
i thinking of using forloop
Sub for_demo()
' Sets x to 1, adds 1 to x until x = 5, loops 5 times.
For x = 1 To 5 Step 1
' Displays value of x in msgbox.
MsgBox x
' Returns to top of loop 4 times.
Next
End Sub
i tried =$D$13
from D13 to D16 there's value,but the cell that contain the formula only can read D13 but not d14 or 15.calculation is automatic already
right now i need to manually type "=" then press enter on the cell,i have hundreds more to go,is there any way to say like i key in A1 to A10 link to M1 to M10
then run with without the trouble doing 1 by one ?
it would be best if can run A1 to D10 linking M1 to P10,like a range to area.
give me some clue pls .thanks
first u draw a button using the toolbar below.right click the button click assign marco,inside type this
Sub Macro1()
ActiveWorkbook.Save
End Sub
if the file is not been save before,it will prompt u for location,or unless u want to specify first
Sub Macro1()
ActiveWorkbook.SaveAs Filename:="D:\Book1.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
i think this shld do the job
sorry it works again,just now the generate pivotdata is check but does not work...
work again sorry sorry
AT first it works,i type = sign in a cell and then link it to a cell in a pivot table,so i decided to play around by turning off the generate pivotdata function from the add/removing toolbar of the pivottable.
then here comes the nightmare,even i turn on the function again it still uses reference method "=A1" instead of the GETPIVOTDATA formula ???
my objectives is
To make sure that the information that is returned is correct even if the PivotTable is updated, you can use the GETPIVOTDATA formula, instead of a simple link into a PivotTable. For example, if you use a simple link to a summation cell in a PivotTable, and then you update the PivotTable, the summation cell may move, but your link will not point to the new location.
wat went wrong ?!
never mind already,i can use the replace function to replace the formula 03 to 04.
thanks for the effort anyway
hi everyone,
i have a table that need to extract data from a pivot table,as the data always changes i cannot use the "=A1" sort of so i resort to Getpivotdat formula.
so i have this formula
=GETPIVOTDATA(" Jan-03",$A$16,"XCVR Source Site","AMK","Region","ASIA","Tech Catg","GSM")
quite long but i have a little problem,if i manually key in all the formula then if next time the year turn to 2004,then i gotta change all the formula in the first field to "Jan-04",this would take lots of time,i do not wish to modify the source file to Jan 03 as this would cause some confusion to others(formula),is there a marco that can change all the formula fields to one that i specify ?
thanks alot in advance
not really want i wanted..
nvm anyway i think is not possible
i want to have a single prompt box with 3 input that u can key so that i don need to pop up 3 times.
StartDate = InputBox("Pls enter the date for first day for this quarter
this only 1 time,any ideas ?:)