PDA

View Full Version : Else without If Problem



BAR_NZ
03-07-2018, 01:07 PM
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! :banghead:

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
03-07-2018, 02:37 PM
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
03-07-2018, 03:02 PM
Thank you but i get an error... "Invalid use of Me keyword" What does that mean?

SamT
03-07-2018, 03:14 PM
The Code is not in the ThisWorkbook Code Module. Replace "Me" with the appropriate Workbook Name or "ActiveWorkbook".

BAR_NZ
03-07-2018, 03:27 PM
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
03-08-2018, 08:28 AM
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. :giggle

BAR_NZ
03-08-2018, 12:11 PM
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. :crying:

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

SamT
03-08-2018, 02:32 PM
Show us the latest version of the full code.

BAR_NZ
03-08-2018, 03:27 PM
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
03-08-2018, 03:55 PM
Weird... :dunno:

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
03-11-2018, 12:02 PM
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
03-11-2018, 12:56 PM
Just delete all data from all sheets Except range "A11" and upload that.

BAR_NZ
03-11-2018, 01:40 PM
I really do appreciate your help with this, it's doing my head in, so I can imagine how your feeling... :mkay

Coffee?
21799

SamT
03-11-2018, 03:15 PM
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
03-11-2018, 05:01 PM
Hi again, so it's still only bringing across "Forms", "Mobility", "CTMSEDI" and "InvoiceConsolodation". That's 4 out of 20 :crying:

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
03-12-2018, 06:44 AM
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
03-12-2018, 08:01 AM
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
03-12-2018, 12:57 PM
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
03-12-2018, 01:20 PM
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
03-12-2018, 02:21 PM
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. :bow:

Paul_Hossler
03-12-2018, 03:58 PM
Actually, you really don't want line of code


If Len(Trim(.Range("A2").Value)) = 0 Then GoTo shtNext ' <<<<<<<<<<<<<<<<<<<<

now that I think about it since if there's no data in A2, but there is in A3 (or elsewhere) the whole sheet will be skipped

The For r = 2 To 34 takes care of only getting lines that have data

BAR_NZ
03-12-2018, 05:10 PM
Yeah, thats good... I still seem to have some size bloating as it's 6.73 MB now the code is added... Thoughts on that.

It's slow to open, but it works so I'm ok with this for now.

Paul_Hossler
03-12-2018, 07:38 PM
Bloating ... yes

It's not the code, but some where/how you're getting formats applied to columns past the last one with data and/or past the last rows with data


A XLSM file is really a ZIP file with a different extension

If you change the extension to .ZIP then you can look at it's innards

In this case, 'Sheet5.xml' is over 41MB uncompressed

Opening that with an XML editor you can see its 'Code Name' is 'Sheet8' and uses cells A1:AAA4000. (Hmmm ... look at Clear_Data)

The VBA editor says that Sheet8 name is 'Master'

Deleting columns past the right of real data and rows below the end of real data makes the file 206KB

21811