Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #108201 > unrolled thread

For Each sheet in WkBook problem

Back to article view | Back to microsoft.public.excel.programming

Contents

  For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-26 13:40 -0800
    Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-26 17:37 -0500
      Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-26 16:47 -0800
        Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-26 20:02 -0500
          Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-26 17:50 -0800
            Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-26 21:10 -0500
              Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-26 18:17 -0800
                Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-26 21:28 -0500
                  Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-26 18:33 -0800
                    Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-26 23:03 -0500
                      Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-26 20:55 -0800
                        Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 01:45 -0500
                        Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 07:51 +0100
                          Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 02:04 -0500
                            Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-27 00:14 -0800
                              Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 09:22 +0100
                                Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 03:26 -0500
                                  Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 09:30 +0100
                                    Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 03:40 -0500
                                      Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 03:41 -0500
                                        Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 09:46 +0100
                                          Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-27 01:10 -0800
                                            Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 10:20 +0100
                                              Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-27 01:43 -0800
                                                Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 04:49 -0500
                                                Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 10:51 +0100
                                                  Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 10:53 +0100
                                                  Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 04:54 -0500
                                                  Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-27 02:19 -0800
                                            Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 04:22 -0500
                                              Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 04:24 -0500
                                              Re: For Each sheet in WkBook problem "L. Howard" <lhkittle@comcast.net> - 2015-11-27 01:48 -0800
                                                Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 04:51 -0500
                        Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 08:01 +0100
                          Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 02:16 -0500
                          Re: For Each sheet in WkBook problem Claus Busch <claus_busch@t-online.de> - 2015-11-27 08:22 +0100
                            Re: For Each sheet in WkBook problem GS <gs@v.invalid> - 2015-11-27 03:03 -0500

Page 1 of 2  [1] 2  Next page →

#108201 — For Each sheet in WkBook problem

Sheets in workbook are A, B, C, D, E, F, G plus the two that are excluded in the code. 

Total combined rows with data for all A to G sheets is about 52. (about 7 per sheet.)  

If I run SheetsCopy1 I get about 5000 rows copied to Summary sheet, multiple repeats of correct data.

If I run SheetsCopy2 I see each sheet range to copy is properly selected and the Msgbox shows the correct sheet name.

The DeBug.Print lists the proper sheets A to G.

Code is in a standard module, and the downloaded example workbook looks to be German, saved as ...xlsm.

What little tid-bit am I overlooking here?

Thanks,
Howard

Sub SheetsCopy1()
    Dim ws As Worksheet
    Dim Rng As Range

    For Each ws In ThisWorkbook.Sheets
    
        If (ws.Name <> "Summary") And (ws.Name <> "Begin blad") Then
        
           With ws
           
            ws.Activate
             Set Rng = Range("A2", Range("D2").End(xlDown))
             Rng.Copy Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)
             
           End With
            
        End If
        
    Next
End Sub

Sub SheetsCopy2()
    Dim ws As Worksheet
    Dim Rng As Range
    Dim lRow As Long

    For Each ws In ThisWorkbook.Sheets
    
        If (ws.Name <> "Summary") And (ws.Name <> "Begin blad") Then
        'Debug.Print ws.Name
           With ws
            ws.Activate
             Set Rng = Range("A2", Range("D2").End(xlDown))
             Rng.Select
             MsgBox ws.Name
           End With
            
        End If
        
    Next
End Sub

[toc] | [next] | [standalone]

#108202

Hi Howard,
Any time I have to consolidate data from multiple sheets to a 'summary' 
sheet I use the following logic:

Dim wks As Worksheet, vData, lNextRow&

With Sheets("Summary")
  'Get the next row position on the summary sheet
  lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

  For Each wks In ThisWorkbook.Sheets
    'Load the data range on each consolidation sheet into an array.
    If (wks.Name <> "Summary") And (wks.Name <> "Begin blad") Then
      vData = wks.Range("A2", wks.Range("D2").End(xlDown))

      'Assign the array to the next row position
      .Cells(lNextRow, 1).Resize(Ubound(vData), _
                                 Ubound(vdata), 2) = vData

      'Increment the next row position
      lNextRow = lNextRow + Ubound(vData)
    End If
  Next 'wks
End With 'Sheets("Summary")

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108203

,
> Any time I have to consolidate data from multiple sheets to a 'summary' 
> sheet I use the following logic:
> 
> Dim wks As Worksheet, vData, lNextRow&
> 
> With Sheets("Summary")
>   'Get the next row position on the summary sheet
>   lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1
> 
>   For Each wks In ThisWorkbook.Sheets
>     'Load the data range on each consolidation sheet into an array.
>     If (wks.Name <> "Summary") And (wks.Name <> "Begin blad") Then
>       vData = wks.Range("A2", wks.Range("D2").End(xlDown))
> 
>       'Assign the array to the next row position
>       .Cells(lNextRow, 1).Resize(Ubound(vData), _
>                                  Ubound(vdata), 2) = vData
> 
>       'Increment the next row position
>       lNextRow = lNextRow + Ubound(vData)
>     End If
>   Next 'wks
> End With 'Sheets("Summary")
> 
> -- 
> Garry

Hi Garry,

Thanks for taking a look see. 

This line errors with xlUP error value -4162

lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

Tried with 15 +- rows of data already on Summary sheet and with only the headers on the sheet.  Both give same error...?

Summary is a table for the A B C D columns, and the line failed, changed the table to a range, same result.  Not sure that would make any difference anyway.  

I also know that Excel has a personal vendetta out for me, given my luck with recent projects!

Howard  

[toc] | [prev] | [next] | [standalone]

#108204

My bad! (aircode<g>)

This..
  lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1

..should be...

lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108205

On Thursday, November 26, 2015 at 5:03:00 PM UTC-8, GS wrote:
> My bad! (aircode<g>)
> 
> This..
>   lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1
> 
> ..should be...
> 
> lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
> 
> -- 
> Garry

Hi Garry,

Thanks, actually if I had studied the code harder, I should have seen that.

I did have to make a change on this line for the Resize columns.

.Cells(lNextRow, 1).Resize(UBound(vData), 4) = vData

Works great now.

Thanks for the array approach, I like the array shot at it, but it still whacks me most often.

Howard

[toc] | [prev] | [next] | [standalone]

#108206

> On Thursday, November 26, 2015 at 5:03:00 PM UTC-8, GS wrote:
>> My bad! (aircode<g>)
>> 
>> This..
>>   lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1
>> 
>> ..should be...
>> 
>> lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
>> 
>> -- 
>> Garry
>
> Hi Garry,
>
> Thanks, actually if I had studied the code harder, I should have seen 
> that.
>
> I did have to make a change on this line for the Resize columns.
>
> .Cells(lNextRow, 1).Resize(UBound(vData), 4) = vData
>
> Works great now.
>
> Thanks for the array approach, I like the array shot at it, but it 
> still whacks me most often.
>
> Howard

Why did you hardcode the cols? The code uses the cols in vData, which 
is 4 based on your range of A:D! You should always let the code use the 
array sizes for the data...

  Ubound(vData), 2) = 4 since it specifies the 2nd dim (#cols)

..because the range it holds spans cols A:D. You likely forgot that 
loading a worksheet range into a variant results a 2D array.

I don't even use hard values for the source range because the data 
resides in a dynamic range named "InputData" on every sheet. I didn't 
want to take you there with this thread in case it went over your head, 
but I do this...

  vData = wks.Range("InputData")

..so the code is 100% reusable as well as better self-documenting.

HTH

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108207

On Thursday, November 26, 2015 at 6:11:01 PM UTC-8, GS wrote:
> > On Thursday, November 26, 2015 at 5:03:00 PM UTC-8, GS wrote:
> >> My bad! (aircode<g>)
> >> 
> >> This..
> >>   lNextRow = .Cells(1, .Rows.Count).End(xlUp).Row + 1
> >> 
> >> ..should be...
> >> 
> >> lNextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
> >> 
> >> -- 
> >> Garry
> >
> > Hi Garry,
> >
> > Thanks, actually if I had studied the code harder, I should have seen 
> > that.
> >
> > I did have to make a change on this line for the Resize columns.
> >
> > .Cells(lNextRow, 1).Resize(UBound(vData), 4) = vData
> >
> > Works great now.
> >
> > Thanks for the array approach, I like the array shot at it, but it 
> > still whacks me most often.
> >
> > Howard
> 
> Why did you hardcode the cols? The code uses the cols in vData, which 
> is 4 based on your range of A:D! You should always let the code use the 
> array sizes for the data...
> 
>   Ubound(vData), 2) = 4 since it specifies the 2nd dim (#cols)
> 
> ..because the range it holds spans cols A:D. You likely forgot that 
> loading a worksheet range into a variant results a 2D array.
> 
> I don't even use hard values for the source range because the data 
> resides in a dynamic range named "InputData" on every sheet. I didn't 
> want to take you there with this thread in case it went over your head, 
> but I do this...
> 
>   vData = wks.Range("InputData")
> 
> ..so the code is 100% reusable as well as better self-documenting.
> 
> HTH
> 
> -- 
> Garry


Only changed it because that line erroed out, I tried 4 and it works with the 4.

Howard

[toc] | [prev] | [next] | [standalone]

#108208

> Only changed it because that line erroed out, I tried 4 and it works 
> with the 4.

Something wrong there! I ran the code with dummy data and it works just 
fine...

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108209

On Thursday, November 26, 2015 at 6:28:10 PM UTC-8, GS wrote:
> > Only changed it because that line erroed out, I tried 4 and it works 
> > with the 4.
> 
> Something wrong there! I ran the code with dummy data and it works just 
> fine...
> 
> -- 
> Garry

What would I look for on the sheet/s or workbook?

Howard

[toc] | [prev] | [next] | [standalone]

#108210

> On Thursday, November 26, 2015 at 6:28:10 PM UTC-8, GS wrote:
>>> Only changed it because that line erroed out, I tried 4 and it 
>>> works  with the 4.
>> 
>> Something wrong there! I ran the code with dummy data and it works 
>> just  fine...
>> 
>> -- 
>> Garry
>
> What would I look for on the sheet/s or workbook?
>
> Howard

Can't say for sure without looking at your file. If there's any reason 
vData is not a 2D array then that line will definitely throw an error. 
Try this on any sheet...


Sub test()
  Dim v
  v = Range("A1:D2")
  Debug.Print "rows:=" & UBound(v)
  Debug.Print "cols:=" & UBound(v, 2)
End Sub

..and it should return...

  rows:=2
  cols:=4

..in the IW. Note that the range doesn't need to contain any data.

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108211

> Can't say for sure without looking at your file. If there's any reason 
> vData is not a 2D array then that line will definitely throw an error. 
> Try this on any sheet...
> 
> 
> Sub test()
>   Dim v
>   v = Range("A1:D2")
>   Debug.Print "rows:=" & UBound(v)
>   Debug.Print "cols:=" & UBound(v, 2)
> End Sub
> 
> ..and it should return...
> 
>   rows:=2
>   cols:=4

> Garry

It returned 2 & 4 for every sheet, Summary & A to G,  and the same for a new workbook made with same names and the data copied to each sheet.

I did notice while the .cells... line was yellow highlighted that the .End(xlDown) was showing -4162 when mouse over it.



      vData = wks.Range("A2", wks.Range("D2").End(xlDown))

      'Assign the array to the next row position
      .Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData), 2) = vData 

Then another time it showed a date.  Column D header is "Datum" and custom formatted to dd-mm-yyy.

I have no idea why it showed -4162 that one time.

Howard

[toc] | [prev] | [next] | [standalone]

#108212

>> Can't say for sure without looking at your file. If there's any 
>> reason  vData is not a 2D array then that line will definitely throw 
>> an error.  Try this on any sheet...
>> 
>> 
>> Sub test()
>>   Dim v
>>   v = Range("A1:D2")
>>   Debug.Print "rows:=" & UBound(v)
>>   Debug.Print "cols:=" & UBound(v, 2)
>> End Sub
>> 
>> ..and it should return...
>> 
>>   rows:=2
>>   cols:=4
>
>> Garry
>
> It returned 2 & 4 for every sheet, Summary & A to G,  and the same 
> for a new workbook made with same names and the data copied to each 
> sheet.
>
> I did notice while the .cells... line was yellow highlighted that the 
> .End(xlDown) was showing -4162 when mouse over it.
>
>
>
>       vData = wks.Range("A2", wks.Range("D2").End(xlDown))
>
>       'Assign the array to the next row position
>       .Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData), 2) = 
> vData 
>
> Then another time it showed a date.  Column D header is "Datum" and 
> custom formatted to dd-mm-yyy.
>
> I have no idea why it showed -4162 that one time.
>
> Howard

Have a look in the ObjectBrowser for XlDirection, then click each item 
listed to see its value. Here's what you should see...

  xlDown = -4121
  xlToLeft = -4159
  xlToRight = -4161
  xlUp - -4162

Sounds like you might need to close/reopen Excel to see if these 
anomolies go away!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108213

Hi Howard,

Am Thu, 26 Nov 2015 20:55:57 -0800 (PST) schrieb L. Howard:

you have a parenthis on the wron place

>       .Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData), 2) = vData 
	 .Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData, 2)) = vData



Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108215

> Hi Howard,
>
> Am Thu, 26 Nov 2015 20:55:57 -0800 (PST) schrieb L. Howard:
>
> you have a parenthis on the wron place
>
>>       .Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData), 2) = 
>> vData 
> 	 .Cells(lNextRow, 1).Resize(UBound(vData), UBound(vData, 2)) = vData
>
>
>
> Regards
> Claus B.

  ..Ubound(vdata), 2) should be Ubound(vdata, 2))
Nice catch! Thank you, Claus<g>

Sorry Howard, ..my bad!

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108219

> Sorry Howard, ..my bad!
> 
> -- 
> Garry


Hi Garry and Claus,

Thanks for clearing that up, I thought my Excel version was cursed and was having a ton of fun messing with me! (I still think it is out to get me though.)

Garry, the array code is an excellent reference for me.  And the trouble shooting was a pretty good learning thing too.

Claus, neat and clever with the ASCII numbers codes.  There will actually be A to Z sheets, each with names beginning with that letter.

 Howard

[toc] | [prev] | [next] | [standalone]

#108220

Hi Howard,

Am Fri, 27 Nov 2015 00:14:34 -0800 (PST) schrieb L. Howard:

> Claus, neat and clever with the ASCII numbers codes.  There will actually be A to Z sheets, each with names beginning with that letter.

the loop with the ASCII numbers only works for sheets with only this
character. If you loop from A to Z and look for Left(Sheet.Name,1) you
will include "Summary" and "Begin Blad"


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108221

> Hi Howard,
>
> Am Fri, 27 Nov 2015 00:14:34 -0800 (PST) schrieb L. Howard:
>
>> Claus, neat and clever with the ASCII numbers codes.  There will 
>> actually be A to Z sheets, each with names beginning with that 
>> letter.
>
> the loop with the ASCII numbers only works for sheets with only this
> character. If you loop from A to Z and look for Left(Sheet.Name,1) 
> you will include "Summary" and "Begin Blad"
>
>
> Regards
> Claus B.

I think you mean 'exclude'?

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108222

Hi Garry,

Am Fri, 27 Nov 2015 03:26:09 -0500 schrieb GS:

> I think you mean 'exclude'?

Howard wrote that the sheets have names *beginning* with A to Z.
Then he cannot work with ASCII numbers because he had to look for
Left(Sheet.Name,1) and that would INCLUDE the sheets SUMMARY and BEGIN
BLADS.


Regards
Claus B.
-- 
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

[toc] | [prev] | [next] | [standalone]

#108224

> Hi Garry,
>
> Am Fri, 27 Nov 2015 03:26:09 -0500 schrieb GS:
>
>> I think you mean 'exclude'?
>
> Howard wrote that the sheets have names *beginning* with A to Z.
> Then he cannot work with ASCII numbers because he had to look for
> Left(Sheet.Name,1) and that would INCLUDE the sheets SUMMARY and 
> BEGIN BLADS.
>
>
> Regards
> Claus B.

Well yes, but I thought these 2 sheets were excluded in the If..Then! 
So what I meant is those 2 sheets still need to be excluded. Your 
statement that they would be included is accurate and so I was thinking 
you meant to add they need to be excluded. Regardless, I prefer 
explicit inclusion as exampled in Consolidate_DetailShts() -OR- 
explicit exclusion as exampled in ConsolidateSheets().<g>

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

#108225

BTW, it 3:40am here and so I need to 'bag some Zs' before doing much 
more...

-- 
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
  comp.lang.basic.visual.misc
  microsoft.public.vb.general.discussion

[toc] | [prev] | [next] | [standalone]

Page 1 of 2  [1] 2  Next page →

Back to top | Article view | microsoft.public.excel.programming

csiph-web