Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reverse Macro (2003)

    I have a macro (see attached) that extracts all rows according to a name in column W and puts the data in separate worksheets. This workbook will be put on a drive as a shared workbook so everyone can edit their own sheet. Once everyone has entered their information, is it possible get a macro to put the edited information back into one spreadsheet?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reverse Macro (2003)

    Does this do what you want? Test on a copy of the workbook.

    Sub CombineSheets()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngTargetRow As Long
    Dim rngCurrent As Range
    Dim lngRows As Long
    Application.ScreenUpdating = False
    Set wshTarget = Worksheets("Sheet1")
    wshTarget.Range(wshTarget.Cells(2, 1), wshTarget.Cells(wshTarget.Rows.Count, _
    wshTarget.Columns.Count)).ClearContents
    For Each wshSource In Worksheets
    If Not wshSource.Name = wshTarget.Name Then
    lngTargetRow = wshTarget.Cells.Find(What:="*", LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Set rngCurrent = wshSource.Range("A1").CurrentRegion
    lngRows = rngCurrent.Rows.Count
    Set rngCurrent = rngCurrent.Offset(1, 0).Resize(lngRows - 1)
    rngCurrent.Copy _
    Destination:=wshTarget.Cells(lngTargetRow, 1)
    End If
    Next wshSource
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Macro (2003)

    I am getting the following code highlighted in yellow:
    lngTargetRow = wshTarget.Cells.Find(What:="*", LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row + 1

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reverse Macro (2003)

    And what does the error message say?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Macro (2003)

    Run Time Error "91"
    Object Variable or with block variable not set

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reverse Macro (2003)

    This error will occur if there is a completely blank worksheet in your workbook. Please remove such worksheets before running the macro.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Macro (2003)

    I put in a sheet1 because the macro was looking for a sheet1. I now get runtime error 9 - subscript out of Range
    Set wshTarget = Worksheets("Sheet1")

    When the other macro runs, it separates into separate sheets with the names on each sheet. There is no Sheet1.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reverse Macro (2003)

    The code in the Word document that you posted mentions Sheet1:

    Set ws1 = Sheets("Sheet1")

    So it seemed not unreasonable to assume that your workbook contained a sheet named Sheet1.
    Does your workbook already contain a sheet into which all data should be copied? If so, what is its name?

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Macro (2003)

    Sheet1 had the whole report before it was broken down into separate sheets. After the macro ran, it had a sheet1 that contained everything and then it had individual sheets according to the names in column W. I deleted Sheet1. Should I have kept sheet1 in the workbook? I can do that.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reverse Macro (2003)

    I'd create a new sheet named Sheet1, and copy the column headings from one of the other sheets into row 1.
    The macro should run then. If you want to give the sheet a different name, change the name in the code accordingly.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reverse Macro (2003)

    Thanks Hans
    Works perfectly

Posting Permissions

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