Thread: Creating an index using macros

This is probably an extremely basic question!!

I am new to macros (obviously) and i am trying to create a workbook that has an index
and identical 'forms' on different sheets. I am trying to record a macro so every time i
press a button the index sheet automatically adds a row and new form reference number, aswell as copying my first form to a new sheet and updating the reference number on that form with reference to the index!!

My problem is getting the the index sheet to add rows that go from 001 to 002 to 003 etc. on the same button.

Please Help!!!

Thanks,

Damien

Welcome to the forum, damocull.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

damocull, you haven't provided enough information for anyone to help.

Post a workbook and explain exactly what you want to happen.

Hello damocull,

I have added the macro below to the attached workbook. This macro is now attached to the button on the "index" worksheet.

Code:

Sub IncrementValues()

  Dim Rng As Range
  Dim RngEnd As Range
  Dim ShtName As String
  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    
    Set Rng = Wks.Range("A2:E5")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    
    If RngEnd.Row < Rng.Row Then Exit Sub
    
    Set Rng = RngEnd.Offset(1, 0)
    
      With Rng
       .Item(1, 1) = .Item(0, 1) + 1
       .Item(1, 4) = .Item(0, 4) + 1
       .Item(1, 5) = .Item(0, 5) + 1
      End With
      
      ShtName = Rng.Item(1, 1)
      
      On Error Resume Next
        Set Wks = Worksheets(ShtName)
        If Err = 9 Then
          Err.Clear
          Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = ShtName
          Worksheets("Index").Activate
        End If
      On Error GoTo 0
      
End Sub

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save