Is this possible with Excel Macro/VBA?

[stratplayer]

Hi Everyone.
My name is Duncan and I have been working on Excel for sometime now as a normal user, doing nothing fancier than entering data into fields etc.

At the moment I have a spreadsheet that has around 60 tabbed sheets on it, each one relates to a particular problem area, each tabbed sheet has a nominated owner.

I want to pull out the summary for each owner on each tabbed sheet to collate a total list of summary info for each owner, but excluding ones that are 100% completed. The results are stored in a new spreadsheet with a tab for each owner and a list of task summaries.

Now I will try and give you some more technical information on what I am using and what fields etc...

Excel 2013 is my platform on Windows 7

• Ignore first two tabs
• Repeat the following from tab 3 onwards
  • Check if cell AA10 is set to 100 or 100%
    • Skip to next tab if it is 100%
  • Get the owner name from cell N14
  • Select all columns from A to AM
  • Select all rows from 1 to 22
  • Store this information in clipboard etc??
  • Move onto next tab and check if Owner is same
• Repeat until all tabs checked
• Create new spreadsheet and create worksheet with Owners name, populate the worksheet with all the information taken during previous steps
• Repeat for all owners in cell N14 (excluding first two tabs)

Is this possible, does it make any sense?

Thanks for looking either way.

 

[ranman256]

This should get you started

Public Sub ScanAllSheets()
Dim sht As Worksheet
Dim i As Integer
Dim vOwn


On Error GoTo ErrScan
i = 1
For Each sht In Sheets
   If i > 2 Then
      sht.Select
      If Range("AA10").Value <> 1 Then       'if 100% the value=1
         vOwn = Range("N14").Value
         Range("A1:AM22").Select
         Selection.Copy
         
            'paste to new sheet
        Sheets(vOwn).Select
        Range("A1").Select
        If ActiveCell.Value <> "" Then Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select  'next empty row
        
        ActiveSheet.Paste
        Application.CutCopyMode = False
        
      End If
   End If
   i = i + 1
Next
Set sht = Nothing
Exit Sub


ErrScan:
If Err = 9 Then  'add new sheet
    Sheets.Add
    ActiveSheet.Name = vOwn
   Resume
End If
End Sub

 

[stratplayer]

This should get you started

Public Sub ScanAllSheets()
Dim sht As Worksheet
Dim i As Integer
Dim vOwn


On Error GoTo ErrScan
i = 1
For Each sht In Sheets
   If i > 2 Then
      sht.Select
      If Range("AA10").Value <> 1 Then       'if 100% the value=1
         vOwn = Range("N14").Value
         Range("A1:AM22").Select
         Selection.Copy
         
            'paste to new sheet
        Sheets(vOwn).Select
        Range("A1").Select
        If ActiveCell.Value <> "" Then Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select  'next empty row
        
        ActiveSheet.Paste
        Application.CutCopyMode = False
        
      End If
   End If
   i = i + 1
Next
Set sht = Nothing
Exit Sub


ErrScan:
If Err = 9 Then  'add new sheet
    Sheets.Add
    ActiveSheet.Name = vOwn
   Resume
End If
End Sub

Hi thanks.

I tried this code and using the debug option it goes through the first 2 sheets to get to the third one (the 1st one with the information on it).
It then does the line sht.select and then goes to the ErrScan: and end subs...

I am afraid I dont know enough about what is happening to trace where the problem lies.