On Apr 3, 4:35*am, khurram_razaq
<khurramra...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "meh2...@gmail.com" wrote:
> > On Apr 1, 11:12 am, khurram_razaq
> > <khurramra...@discussions.microsoft.com> wrote:
> > > 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
>
> > 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.
>
> > Best,
>
> > Matt Herbert
>
> > Sub LoopThroughCells()
>
> > 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
|
Similar Threads
