Else without If Problem

[BAR_NZ]

Hi, I was wondering if someone could please look at my code and see where I'm going wrong, I have looked and looked and can't see the wood for the trees now... Please help!

What I'm trying to achieve to pull all data from multiple sheets in one worksheet, except for a couple of named sheets, into a master sheet. However I only want the data from a specific range A2:CA34 if it has content and ignore any blank rows. I hope this makes sense?

All sheets i'm pulling from have the same range's to select (A2:CA34), however some sheets will have three rows populated and some may have all, I want the data without the blanks if i can.

Sub CombineData()




Dim Sht As Worksheet
    For Each Sht In ActiveWorkbook.Worksheets
        If Sht.Name <> "Master" And Sht.Range("A11").Value <> "" Then
        
        Select Case Sht.Name


        Case "Template", "Master", "Teams"
        
        Case Else
                LastRow = Range("A34").End(xlUp).Row
                Range("A2:CA34", Cells(LastRow, "CA")).Copy
                Sheets("Master").Select
                Range("A65536").End(xlUp).Offset(1, 0).Select
                ActiveSheet.Paste
            Sht.Select
        Else
    End If
    Next Sht
            Sheets("Master").Select


End Sub

[SamT]

Sub CombineData()
'For help see: http://www.vbaexpress.com/forum/showthread.php?62193
Const SkipSheets As String = "Master, Teams, Template"
Dim Sht As Worksheet

For Each Sht in Me.Sheets
If InStr(SkipSheets, Sht.Name) <> 0 Then GoTo shtNext

If Sht.Range("A11") <> "" Then _
       Sht.Range("A2").CurrentRegion.Offset(1).Copy _
       Destination:=Sheets("Master").Cell(Rows.Count, 1).End(xlUp).Offset(1)
shtNext:
Next
End Sub

[BAR_NZ]

Thank you but i get an error... "Invalid use of Me keyword" What does that mean?

[SamT]

The Code is not in the ThisWorkbook Code Module. Replace "Me" with the appropriate Workbook Name or "ActiveWorkbook".

[BAR_NZ]

Have done that, now it just highlights these rows... Run-time error '438':
Object doesn't support this property or method.

I'm sorry about this, but I'm new to this, as if you haven't guessed, lol

       Sht.Range("A2").CurrentRegion.Offset(1).Copy _
       Destination:=Sheets("Master").Cell(Rows.Count, 1).End(xlUp).Offset(1)

[SamT]

My bad typo... "Cell" must be "Cells"

       Destination:=Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1)

Don't feel bad... I've been doing this for 15 years. Still, I had to create a workbook with multiple sheets and two SkipSheets. Add data and use A11.

Even then it took me several tries to see the issue.

I blame Not Enuff Coffee.

[BAR_NZ]

Thank so much, now I have another wee problem now , it functions and pulls in 4 sheets at random from the middle of the sheets, but not all 20 into the Master Sheet.

Coffee.jpg
I hope this helps a little, best I can do from NZ sorry... But I do very much appreciate your help.

[SamT]

Show us the latest version of the full code.

[BAR_NZ]

Here you go... I have only added the additional sheets to ignore. I had to remove the URL you added because of forum rules around the number of URL's allowed to be posted would not let me submit my responce...

Sub CombineData()
Const SkipSheets As String = "Master, LogisticsTeam, Template, PM_Resource, BA_Resource, Test_Resource, Capacity, PivotResourceType, PivotProject, Sheet4"
Dim Sht As Worksheet


For Each Sht In ActiveWorkbook.Sheets
If InStr(SkipSheets, Sht.Name) <> 0 Then GoTo shtNext


If Sht.Range("A11") <> "" Then _
       Sht.Range("A2").CurrentRegion.Offset(1).Copy _
       Destination:=Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1)
shtNext:
Next
End Sub

[SamT]

Weird... :

Add the line

Do Events

after the line

shtNext:

and see what that does.

nb. Any sheets whose entire name can be found in SkipSheets will be skipped, Ex Sheets "Team" and "Temp" will be skipped. If this is an issue, we will need to change SkipSheets to an array so that complete names will be compared

Dim SkipSheets As Variant
Dim i as long
SkipSheets = Array("Master", "LogisticsTeam", "Template", "PM_Resource", "etc", "etc")
'
'
For Each Sht In ActiveWorkbook.Sheets
   For i = Lbound(SkipSheets) to UBound(SkipSheets)
      If Sht.Name = SkipSheets(i) Then Goto shtNext
   Next i
'
'
'
ShtNext:
DoEvents 'Still a good idea. "Do Events" or "DoEvents"? Debug + Compile will tell you.
'

[BAR_NZ]

Hi, I added the DoEvents after shtNext: it runs as before, however still only brings in the 4 sheets as before . I would upload the actual sheet I'm working with, however it has quite sensitive data in it. May I send you a private message with the file attached?

[SamT]

Just delete all data from all sheets Except range "A11" and upload that.

[BAR_NZ]

I really do appreciate your help with this, it's doing my head in, so I can imagine how your feeling...

Coffee?
Coffee.jpg

[SamT]

First I placed an x in every sheets ("A11"), then I ran this code to insert some data

Sub AddInfo()
Dim Sht
For Each Sht In Worksheets
Sht.Range("A2:B2") = Sht.Name
Next
End Sub

THen I ran this code 3 or 4 times, and copied that data into Master for all desired sheets ( And sheet TEMPLATE. See code comments)

Sub CombineData()
' For help see: http://www.vbaexpress.com/forum/showthread.php?62193
Dim SkipSheets
SkipSheets = Array("Master", "LogisticsTeam", "Template", "PM_Resource", _
  "BA_Resource", "Test_Resource", "Capacity") 'Template should be TEMPLATE
Dim Sht As Worksheet
Dim i

For Each Sht In ActiveWorkbook.Sheets
  For i = LBound(SkipSheets) To UBound(SkipSheets)
    If SkipSheets(i) = Sht.Name Then GoTo shtNext
  Next

If Sht.Range("A11") <> "" Then _
  Sht.Range("A2").CurrentRegion.Offset(1).Copy _
    Destination:=Sheets("Master").Cells(Rows.Count, 1).End(xlUp).Offset(1)

DoEvents

shtNext:
Next
End Sub

Are there any formulas on any sheets? Maybe turn off calculation and screen updating before you run it.

Note that I used the existing SkipSheets string for that array

[BAR_NZ]

Hi again, so it's still only bringing across "Forms", "Mobility", "CTMSEDI" and "InvoiceConsolodation". That's 4 out of 20

I have added...

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

To the line just below the URL

The data it's bringing in, does look correct though.

[SamT]

Have you verified that Cell A11 on the other sheets has some value in it?

That is a short Sub, please post your latest version when replying, even if it's the same as before. The code in your attachment was an old version. How do we know what changes you make? Especially when We/I can't duplicate the problem

[Paul_Hossler]

Your sheet formats are inconsistent

Examples:

Master and ACP end in column AA
Azzure and Citrix end in column CA
Template ends in CS

I extended Master to CA (since that's what you said) and used this macro

Option Explicit

'What i 'm trying to achieve to pull all data from multiple sheets in one worksheet, except for a couple of named sheets, into a master sheet.
'However I only want the data from a specific range A2:CA34 if it has content and ignore any blank rows. I hope this makes sense?
'All sheets i'm pulling from have the same range's to select (A2:CA34), however some sheets will have three rows populated and some
'   may have all, I want the data without the blanks if i can.

Sub CombineData()
    Dim Sht As Worksheet
    Dim aSkipSheets As Variant
    Dim sSkipSheets As String
    Dim rMasterStartRow As Range, rRowToCopy As Range
    Dim r As Long
    
    aSkipSheets = Array("Master", "LogisticsTeam", "Template", "PM_Resource", "BA_Resource", "Test_Resource", "Capacity")
    sSkipSheets = Join(aSkipSheets, "#") & "#"
    
    Application.ScreenUpdating = False
    
    With Worksheets("Master")
        Set rMasterStartRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
    
    For Each Sht In ActiveWorkbook.Sheets
        With Sht
            If InStr(sSkipSheets, .Name & "#") <> 0 Then GoTo shtNext
            If Len(Trim(.Range("A11").Value)) = 0 Then GoTo shtNext
           
            Application.StatusBar = "Now checking " & .Name
            
            For r = 2 To 34
                Set rRowToCopy = .Cells(r, 1).Resize(1, 79) '   A to CA
                If Application.WorksheetFunction.CountA(rRowToCopy) > 0 Then
                    rRowToCopy.Copy rMasterStartRow
                    Set rMasterStartRow = rMasterStartRow.Offset(1, 0).Resize(1, 79)
                End If
            Next r
        End With
shtNext:
    Next

    Worksheets("Master").Select

    Application.ScreenUpdating = True
    Application.StatusBar = False
End Sub

The attachment just has a few sheets in it. I had to 'clean' the empty far right columns and bottom rows since there was no way that could be a 6MB workbook

[BAR_NZ]

OK, this looks great, back to SamT's question I think I've identified one of my many problems and i have a few, not just with this, lol...

Anyway, SamT said "Have you verified that Cell A11 on the other sheets has some value in it?"

So, with that wee nugget of information I have figured out the reason I am not getting all the sheets I need... The "Other Sheets" should be starting at A2, not A11, so A2 through CA34 (Have updated the formatting on all sheets to be consistent as per Paul's comments about the consistency of the sheet layout, so ALL sheets now range only from A2 - CA34.

I tested this theory on the Citrix tab by only adding data into row 2, ran it and nothing, then i added data into row 11 and ran it again, and this returned the data to the master.

So in a nutshell... All sheets to collect the data start at A2 and where it is pasted into the master starts at A11...

Thoughts?

[Paul_Hossler]

The marked line was a11 in your attachment.

I'm guessing that it should be A2

Option Explicit
'What i 'm trying to achieve to pull all data from multiple sheets in one worksheet, except for a couple of named sheets, into a master sheet.
'However I only want the data from a specific range A2:CA34 if it has content and ignore any blank rows. I hope this makes sense?
'All sheets i'm pulling from have the same range's to select (A2:CA34), however some sheets will have three rows populated and some
'   may have all, I want the data without the blanks if i can.
Sub CombineData()
    Dim Sht As Worksheet
    Dim aSkipSheets As Variant
    Dim sSkipSheets As String
    Dim rMasterStartRow As Range, rRowToCopy As Range
    Dim r As Long
    
    aSkipSheets = Array("Master", "LogisticsTeam", "Template", "PM_Resource", "BA_Resource", "Test_Resource", "Capacity")
    sSkipSheets = Join(aSkipSheets, "#") & "#"
    
    Application.ScreenUpdating = False
    
    With Worksheets("Master")
        Set rMasterStartRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
    
    For Each Sht In ActiveWorkbook.Sheets
        With Sht
            If InStr(sSkipSheets, .Name & "#") <> 0 Then GoTo shtNext
            If Len(Trim(.Range("A2").Value)) = 0 Then GoTo shtNext ' <<<<<<<<<<<<<<<<<<<<
           
            Application.StatusBar = "Now checking " & .Name
            
            For r = 2 To 34
                Set rRowToCopy = .Cells(r, 1).Resize(1, 79) '   A to CA
                If Application.WorksheetFunction.CountA(rRowToCopy) > 0 Then
                    rRowToCopy.Copy rMasterStartRow
                    Set rMasterStartRow = rMasterStartRow.Offset(1, 0).Resize(1, 79)
                End If
            Next r
        End With
shtNext:
    Next
    Worksheets("Master").Select
    Application.ScreenUpdating = True
    Application.StatusBar = False
End Sub

[BAR_NZ]

OK, so we can now confidently say SOLVED.

You guys rock and i really do appreciate all of your help, I could not have done this with out your expert assistance.