Hi Matt,
Thanks for the code but I have getting errors and unable to resolve them.
Basically what I am trying to do is as follows:
I have two sheets one one with list of Entites called Entities and other
sheet called Base which pulls data from a database based on the enitity pick
from the enitites sheet.
On my list of entites I have 133 different entities and basically what I
want to setup the macro for is to select one entity at a time from the
entities sheet and insert it into cell B5 on the Base sheet, then I want to
the Macro to refesh the numbers etc and one the numbers are retrived, i want
it to copy the data and formating into a new sheet and paste special values.
I have created a marco (see my coding below) which sucessfully can do
everything but only for one entity. What I need help with is to modify the
code so that it can automatically proform the process for all the entities
automatically.
Will be grateful for you help in modifying the code as required.
Many thanks for your help.
Khurram
Hi,
I am trying to create a Macro in Excel to go through a list of names on one
sheet, and starting from the top pick one up at a time and perform the action
prescribed by the macro. I need some help with some coding in Excel. I have
got the action setup in the macro, however I am unable to get the macro to
change what its looking at.
See code below, in this line of Code (ActiveCell.FormulaR1C1 =
"=Entities!R[-3]C[-1]"), I would like it to change the cell each time its
performed the action and keep changing and continuing the action until it
arrives at a blank cell.
Sub EMEA_Reporting()
'
' EMEA_Reporting Macro
' Macro recorded 01/04/2009 by KRazaq
'
'
ActiveCell.FormulaR1C1 = "=Entities!R[-3]C[-1]"
Range("B6").Select
Sheets("Base").Select
Sheets("Base").Copy Before:=Sheets(1)
Selection.ShapeRange.Delete
Cells.Select
ActiveWorkbook.BreakLink Name:="C:\Hyperion\SmartView\bin\HsTbar.xla",
Type _
:=xlExcelLinks
End Sub
Thanks for you help.
Khurram
Here is some code to pick up the contiguous cells in a range (i.e.
starting at A1 and looking down the column until there is a blank
cell). I'm not quite sure what you trying to accomplish, so the code
below has syntax necessary to pick up a range to loop through and then
loops through each item in the range.
Matt Herbert
Dim rngData As Range
Dim rngItem As Range
With Worksheets(1) '<< index name or number here
Set rngData = .Range("A1", .Range("A1").End(xlDown))
End With
For Each rngItem In rngData
MsgBox rngItem.Value
Next
End Sub- Hide quoted text -
- Show quoted text -
Khurram,
The code below should give you enough syntax to suffle syntax around
or recreate syntax for a program to fit your needs. I tried to
comment the code so that you can understand what the program is
doing. Make sure that you run through the program step-by-step (i.e.
continually hit the F8 key in the VBE window - Debug | Step Into) to
see how the program and the Excel workbook are behaving. Also, be
sure to insert your "refesh the numbers etc" code into the appropriate
place within the procedure.
Best,
Matt
Sub LoopThroughCells()
Dim wksBase As Worksheet
Dim wksEnt As Worksheet
Dim wksNew As Worksheet
Dim rngData As Range
Dim rngItem As Range
'creat worksheet object for the "Entities" worksheet
Set wksEnt = Worksheets("Entities")
'creat worksheet object for the "Base" worksheet
Set wksBase = Worksheets("Base")
With wksEnt
'create a range object for entries
'pick up the "133" entries in column A of wksEnt (if
' the data is elsewhere, adjust code accordingly)
Set rngData = .Range("A1", .Range("A1").End(xlDown))
End With
'loop through each of the items in rngData, i.e. loop
' through each of the "133" entries
For Each rngItem In rngData
With wksBase
'insert the entry into B5 on wksBase
.Range("B5").Value = rngItem.Value
'-------------------------
'run your "refesh the numbers etc" code here
'
'-------------------------
'copy the worksheet and place it at the far most
' left position of the workbook
.Copy Before:=Worksheets(1)
'creat worksheet object for the new worksheet that
' was copied
Set wksNew = ActiveSheet
'paste values in the newly created worksheet
With wksNew
'copy all cells on wksNew
.Cells.Copy
'paste cells as values
.Cells.PasteSpecial Paste:=xlPasteValues
'turn CutCopyMode off
Application.CutCopyMode = False
End With
End With
Next
End Sub
