Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all I have one main sheet called Data which contains all the data and I then have all other sheets which are sepereted into seciton: e.g. Account 1, Account 2 account 3 etc. I want to be ablle to copy all the datafrom the main data sheet into account 1 sheet (account 1 data) Account 2 sheet(Account 2 data) copied from the main Data Sheet. I have done the macro below but I get the following error:

    Run time error 9 subscript out of range.
    Thanks for all your help
    ' Keyboard Shortcut: Ctrl+Shift+Q

    Sheets("DATA").Select


    For homes = 1 To 250
    ActiveSheet.Next.Select
    Range("A1").Select
    Account = ActiveCell

    RO1 = 15
    For ro = 2 To 700


    Sheets("DATA").Select
    Range("A" & ro).Select
    If ActiveCell = Account Then
    Rows(ro).Select
    Selection.Copy
    Sheets(DATA).Select
    Rows(RO1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    RO1 = RO1 + 1


    End If

    Next ro
    Sheets(Account).Select
    Next homes



    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    ...
    Selection.Copy
    Sheets(DATA).Select
    Rows(RO1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ...

    Shouldn't the highlighted line be

    Sheets(Account).Select

    PS I strongly recommend you to require explicit declaration of variables.
    To enforce this, insert a line

    Option Explicit

    at the top of the module. You'll have to declare the variables that you use, such as homes, account, RO1 and ro.

    To add the line "Option Explicit" automatically to new modules, select Tools | Options... in the Visual Basic Editor, activate the Editor tab if necessary, and tick the check box "Require variable declaration".
    This may seem a nuisance in the beginning, but it will safe you a lot of grief in the long term.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans, I modifed the code but still get the same error, see below:

    Many thanks


    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+Q

    Dim homes As Integer
    Dim Account As String
    Dim RO1 As String
    Dim ro As Integer

    Sheets("DATA").Select


    For homes = 1 To 250
    ActiveSheet.Next.Select
    Range("A1").Select
    Account = ActiveCell

    RO1 = 15
    For ro = 2 To 700


    Sheets("DATA").Select
    Range("A" & ro).Select
    If ActiveCell = Account Then
    Rows(ro).Select
    Selection.Copy
    Sheets(Account).Select
    Rows(RO1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    RO1 = RO1 + 1


    End If

    Next ro
    Sheets(Account).Select
    Next homes



    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I think the code could be simplified if you named the sheets after the accounts.

    It would be helpful if you could attach a stripped down sample workbook.

    NB you shouldn't use String variables to contain numbers: RO1 is a row number, so it should be an Integer or Long.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi again,

    Please see a sample of the spreadsheet.

    Kind Regards,
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The cause of the problem is probably the sheet named "7419 " - there's a space after the account number, so when the code tries to refer to Worksheets("7419") it fails because there is no sheet of that name.

    Here is a somewhat more efficient version of the macro, with a bit of error handling:

    Code:
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+Q
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim r As Long
      Dim m As Long
      Dim strAccount As String
      Dim t As Long
      Application.ScreenUpdating = False
      Set wshSrc = Worksheets("DATA")
      m = wshSrc.Range("A" & wshSrc.Rows.Count).End(xlUp).Row
      For r = 2 To m
        If wshSrc.Range("A" & r) <> wshSrc.Range("A" & (r - 1)) Then
          strAccount = wshSrc.Range("A" & r)
          On Error Resume Next
          Set wshTrg = Worksheets(strAccount)
          If Err Then
            Set wshTrg = Worksheets.Add
            wshTrg.Name = strAccount
          End If
          On Error GoTo 0
          t = 15
        End If
        wshSrc.Rows(r).Copy
        wshTrg.Range("A" & t).PasteSpecial Paste:=xlPasteValues
        t = t + 1
      Next r
      Application.CutCopyMode = False
      Application.ScreenUpdating = True
    End Sub

  7. #7
    4 Star Lounger
    Join Date
    Aug 2005
    Location
    London/Kingston, Surrey, United Kingdom
    Posts
    518
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans,

    First of all many thanks for all your help, it worked,I am sorry to be a pain, but can you please add some comments to the code, I need to show my collleague how this macro works, brief comments would do.

    Thanks a million

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Code:
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+Q
      ' Declare the variables we're going to use
      Dim wshSrc As Worksheet
      Dim wshTrg As Worksheet
      Dim r As Long
      Dim m As Long
      Dim strAccount As String
      Dim t As Long
      ' Prevent the screen from being updated while the macro runs (this makes it faster)
      Application.ScreenUpdating = False
      ' Set a reference to the source sheet DATA
      Set wshSrc = Worksheets("DATA")
      ' Get the last row in column A
      m = wshSrc.Range("A" & wshSrc.Rows.Count).End(xlUp).Row
      ' Loop through the rows of the source sheet, starting at row 2 (since row 1 contains a column header)
      For r = 2 To m
        ' Check whether we've encountered a new account
        If wshSrc.Range("A" & r) <> wshSrc.Range("A" & (r - 1)) Then
          ' Get account from column A
          strAccount = wshSrc.Range("A" & r)
          ' Suppress error messages temporarily
          On Error Resume Next
          ' Get reference to sheet named after account
          Set wshTrg = Worksheets(strAccount)
          ' If the sheet doesn't exist, an error occurs...
          If Err Then
            ' ... so create a new sheet...
            Set wshTrg = Worksheets.Add
            ' ... and give it the name of the account
            wshTrg.Name = strAccount
          End If
          ' Stop suppressing error messages
          On Error GoTo 0
          ' First row to be filled on target sheet
          t = 15
        End If
        ' Copy row from source sheet
        wshSrc.Rows(r).Copy
        ' Paste values only to target sheet
        wshTrg.Range("A" & t).PasteSpecial Paste:=xlPasteValues
        ' Increase row number for next time
        t = t + 1
      Next r
      ' Turn of copy/paste mode
      Application.CutCopyMode = False
      ' Update the screen display again
      Application.ScreenUpdating = True
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •