Posts by Robert B

Re: Creating Amortization Table For 30 Year Mortgage

Hi

Did you try Copy & Paste? Did it not copy the formual and change the reference?

Robert

Re: Creating Amortization Table For 30 Year Mortgage

Hi

Copy and Paste should do just that

Robert

Re: Find Last Column With Data

Hi

you can do this in VBA

Dim SelectColumn as Integer
SelectColumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column -3

I am not sure it can be done with formulae

Robert

Re: Return Data From Referencing A Particular Cell And Worksheet

Hi
and welcome

Right click on the first sheet in your workbook and copy this

Private Sub Worksheet_Activate()
Dim wsheet As Worksheet
Dim L As Long
L = 1
    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    
    For Each wsheet In Worksheets
     If wsheet.Name <> Me.Name Then
      L = L + 1
      With wsheet
          .Range("A1").Name = "Start" & wsheet.Index
          .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:= _
            "Index", TextToDisplay:="Back to Index"
      End With
         Me.Hyperlinks.Add Anchor:=Me.Cells(L, 1), Address:="", SubAddress:= _
            "Start" & wsheet.Index, TextToDisplay:=wsheet.Name
     End If
    Next wsheet
End Sub

This will list all sheets in the workbook and will create a hyperlink to each sheet. It will also create a hyperlink in A1 on each sheet sHowing "Back to Index". You may wish to suppress this.

In order to achieve the second part of your requirement amend the Range("A1") to your needs

This code was suggested to me a couple of years ago by a member of this forum to whom all the credit should go

Hope this helps

Robert

Re: Pivot Table Data Limit (version 12 - 2007)

Hi

and welcome to the forum.

I have always found recorded pivot macros unhelpful and suggest the following code, I am using Excel 2000 and so cannot exceed 65560 lines but I can see no reason why it shouldn't work with the later versions.

I suggest that you step through the code and enter the fileld names as they occur

Sub MakePivot()
Dim myPivot As PivotTable, myField As PivotField, myItem As PivotItem
Dim myRange As Range


Selection.CurrentRegion.Select
Set myRange = Selection
    ActiveWorkbook.Names.Add Name:="Database", RefersTo:=myRange
Set myPivot = ActiveSheet.PivotTableWizard
    Set myField = myPivot.PivotFields("name")
    myField.Orientation = xlDataField
    myField.Function = xlCount' or another function
    myField.Name = "what"    
    Set myField = myPivot.PivotFields("name2")
    myField.Orientation = xlRowField
   Set myField = myPivot.PivotFields("name3")
   myField.Orientation = xlColumnField
    End Sub

Hope that helps

Robert

Re: Find Value And Copy Related Values

Hi
try replacing this sequence

.Cells(Lr, 1) = Sheets("Voorraadscherm").Cells(21, 1) 
                   .Cells(Lr, 3) = Sheets("Voorraadscherm").Cells(21, 3) 
                    .Cells(Lr, 5) = Sheets("Voorraadscherm").Cells(21, 5)

with this

dim SourceCell as Range,TargetCell as Range
Set TargetCell = Sheets("Voorraadverloop").Range("A" & LR)
set SourceCell = Sheets("Voorraadscherm").Range("A21")
SourceCell .Copy Destination:= TargetCell
SourceCell.Offset(0,2) .Copy Destination:= TargetCell.Offset(0,2)
SourceCell.Offset(0,4) .Copy Destination:= TargetCell.Offset(0,4)

I am not certain that the construct .Cells(n,n) is valid. If you are defining a cell within a declared range, the form .Cells(n) is usually OK

HTH

Robert

Re: Find Value And Copy Related Values

Hi

I think if you replace

Lr = Worksheets("Voorraadverloop").ActiveCell.End(xlDown).Row + 2

with

Lr = ActiveCell.End(xlDown).Row + 2

that will give you the value you need for Lr, assuming you are running the macro with Voorraadverloop as the active sheet.

HTH

Robert

Re: Find Value And Copy Related Values

Hi

you don't seem to have declared LR as Long

HTH

Robert

Re: Find And Delete All Rows Below

Hi, and welcome to the forum

try

Dim LastRow As Long, myCell As Range, myRange As Range
Dim myCell1 As Range


LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set myCell1 = Range("A" & LastRow)
    Cells.Find(What:="Site", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        Set myCell = ActiveCell
        
        Set myRange = Range(myCell, myCell1)
        myRange.EntireRow.Delete

Hope that helps

Robert

Re: Macro To Open Workbook & Copy Data

Hi

this might get you started

Dim myFile As String, Wb1 As Workbook, myRange As Range


myFile = Application.GetOpenFilename
Workbooks.Open Filename:=myFile
Set Wb1 = ActiveWorkbook
Sheets("Index").Select ' Index being the sheet identifier, either name or number
Set myRange = Range("An", "Zi")  ' enter your range 


myRange.Copy Destination:=Range("Bn") enter your destination
Wb1.Save
Wb1.Close

you will probably have to define other variables, when you open your workbook the external links will probably refresh automatically, unless you have that option inhibited.

Robert

Re: Sumif Equivalent In Access

Hi

I am not certain that there is an equivalent to SUMIF in Access; a workround might be to create a new table for the Fund values and their totals, and then running an update query based on this new table.

Robert

Re: Setting Variables For Unknown Number Of Workbooks

Brilliant, Bob. I had come to the conclusion it was better to try and work round the problem but that's neat

Thanks

Robert

Hi

I have a folder containing a number of worksheets, these worksheets are of 2 types, a customer submitted workbook and, associated with each of these, anything between 2 and 7 locally created workbooks.

I have a macro which opens a customer workbook and all the relevant local workbooks.

My problem is that I need to declare a variable for each of the workbooks in the form

Dim N as Integer, WbN as workbook,Wb1 as workbook


'open workbooks sequentially


if activeworkbook.name Like *ALL.xls then
set wb1 = activeworkbook


else


set WbN = activeworkbook
N=N+1


end if

This code is contained within a Found files loop

Thanks

Robert

Re: Print 100 Workbooks

Hi

check out this link about looping:
http://www.ozgrid.com/VBA/loop-through.htm

HTH

Robert

Re: Use Vb To Say No To Update Message

Hi

enter this code in This Workbook in the Project Explorer

Private Sub Workbook_Open()
Application.AskToUpdateLinks = False


End Sub

HTH

Robert

Re: If, Vlookup & Sum

Hi

Welcome to the forum

enter this formula in the cell on worksheet2 next to the client name

=SUMIF(worksheet!A1:A12,A1,worksheet1!C1:C12)

where A1:A12 is the range of clints and C1:C12 is the range containing the various values

HTH

Robert

Re: If, Vlookup & Sum

Hi

Welcome to the forum

enter this formula in the cell on worksheet2 next to the client name

=SUMIF(worksheet!A1:A12,A1,worksheet1!C1:C12)

where A1:A12 is the range of clints and C1:C12 is the range containing the various values

HTH

Robert

ps sorry to appear to have duplicated Ger's solution, my phone rang before I could send

R

Re: Overtime Calculation Formula Returns Error Value

Hi

Try formatting the cell as Number

HTH

Robert

Re: Keyword Within Query

Hi

Start with a query using a criterion of IsNot Null in the field containing the email address and base a report on this query

HTH

Robert

Re: More Than One Column In Pivot Table

Hi

would having the branch defined as Page orientation help?

Robert