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

Groups > microsoft.public.excel.programming > #108217

Re: For Each sheet in WkBook problem

From Claus Busch <claus_busch@t-online.de>
Newsgroups microsoft.public.excel.programming
Subject Re: For Each sheet in WkBook problem
Date 2015-11-27 08:22 +0100
Organization A noiseless patient Spider
Message-ID <n3906e$t4o$1@dont-email.me> (permalink)
References (7 earlier) <n38ev5$p0u$1@dont-email.me> <5c31d32d-0537-47da-9266-9ada26d2a7d3@googlegroups.com> <n38ki3$kv$1@dont-email.me> <b2123065-b469-4c08-95b7-f11951ae393b@googlegroups.com> <n38uvl$pq4$1@dont-email.me>

Show all headers | View raw

Hi Howard,

Am Fri, 27 Nov 2015 08:01:18 +0100 schrieb Claus Busch:

> For i = 65 To 71

if you don't want to hardcode the ascii numbers and for more clarity and
logic you can change the code to:

Sub SheetsCopy()
Dim varData As Variant
Dim LRow As Long, i As Long

For i = Asc("A") To Asc("G")
    With Sheets(Chr(i))
        LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        varData = .Range("A2:D" & LRow)
        Sheets("Summary").Cells(Rows.Count, 1).End(xlUp)(2) _
            .Resize(UBound(varData), UBound(varData, 2)) = varData
    End With
Next
End Sub


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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web