Excel help

coopersim · 23 Nov 2010 at 09:07

I'm trying to semi automate a spreadsheet that I use every day - without
much success!

I want to write a macro that:

* changes the names of the worksheet tabs - these are the same every day eg
from Sheet1 to Totals
* changes the names of the cells A3 and B3 - these are the same every day
eg from Row Labels to Message
* reference another worksheet comparing some entries and overwriting them
with the correct data eg. in my worksheet I have an entry that has a code
number, I want the macro to look this up in my reference spreadsheet and
replace the code with the correct message

Is all this possible in one macro and can someone point me in the right
direction?

Thanks!

SiriusB · 23 Nov 2010 at 09:15

Recording a series of actions as a macro would probably do it. Though I admit I do not use Excel often, so there may be a better way.

coopersim · 23 Nov 2010 at 10:09

That was a good idea, thanks! I've tried that, and it works fine for the one spreadsheet, but when I use it on another identically laid out one with a different name, it breaks!

A[L]C · 23 Nov 2010 at 12:25

post the code from the macro. Chances are it directly referenced it. Youd be best changing it to activeworkbook. etc

Namsnik · 23 Nov 2010 at 20:33

Absolutely share the recorded code. Some other thoughts: When recording try to use a technique that is general and repeatable. So don't use your eyes to find that code number but use Edit, Find.....

(I am not clear if you are looking up just a few codes etc or if you eed a column in your new workbook populated with lookup formulae

coopersim · 24 Nov 2010 at 20:45

Thanks guys! I have recorded a macro and for the active sheet it works fine, but it seems to be linked to that one sheet so when I open an identical sheet with a different name it falls apart.

I have included activeworkbook, but there are references in the macro to sourcedata:= then the file name, how do I just tell it to work on the file it has open instead of having to refer it?

Thanks again!

coopersim · 24 Nov 2010 at 20:58

Here is my code:

Sub mytestmacro()
'
' mytestmacro Macro
'

'
Columns("A:A").Select
Selection.NumberFormat = "dd/mm/yy hh:mm:ss"
Cells.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"mytestmacro", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion12
Sheets("Sheet1").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields(" ""ViewName"""), "Count of ""ViewName""", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields(" ""field1""")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
" ""field2""")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
" ""field3""")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields(" ""field4""")
.Orientation = xlRowField
.Position = 4
End With
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A4").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields(" ""field5"""). _
ShowDetail = False
Range("A3").Select
ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = "My test pivot"
Range("B3").Select
ActiveSheet.PivotTables("PivotTable1").DataPivotField.PivotItems( _
"Count of ""ViewName""").Caption = "Count of ""my test pivot"""
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "pivot"
Sheets("mytestmacro").Select
Sheets("mytestmacro").Name = "raw data"
Sheets("pivot").Select
End Sub

A[L]C · 24 Nov 2010 at 21:47

upload or email me the sheet and ill do it for you

edit: I assume you've put the macro in personal.xls