[решено] Перебор листов и ячеек в VBA - Компьютерный форум OSzone.net

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

- - [решено] Перебор листов и ячеек в VBA (http://forum.oszone.net/showthread.php?t=251255)

Перебор листов и ячеек в VBA

Мне надо перебрать все листы книги Excel2007 и поместить содержимое одних и тех же выбранных ячеек
в столбец на отдельный лист Лист1.
Листы в книге все названы, но я понял, что все равно к ним можно обращаться как к ЛистN.
Я написал макрос, но обращение все равно не работает.
Dim SheetNum As String
Dim PosNum As String
SN = 3
SP = 8
While SN < 309
SheetNum = "Лист" + LTrim(Str(SN))
Sheets("SheetNum").Select
Range("A2:G2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Лист1").Select
PosNum = "A" + LTrim(Str(SP))
Range("PosNum").Select
ActiveSheet.Paste Link:=True
SP = SP + 1
SN = SN + 1
Wend

Макрос останавливается на строке Sheets("SheetNum").Select и не хочет идти на Лист3
Как перебрать листы и ячейки?

alef2474, объясните, что вы хотите сделать?
Перебрать все листы можно несколькими способами, например, так

Код:

For Each ws In Worksheets

......

ваш код

......

Next

okshef, спасибо, что откликнулись
У менеджеров имеются безобразные по моему мнению файлы Excel в которых они на отдельных листах ведут базу данных клиентов.
Причем имя листа - это название фирмы клиента.
На каждом листе, например в ячейке В1 написано "Название ООО "ФирмаХ"

в объединенных ячейках А6-D6 "контактное лицо Тамара Ивановна"

в объединенных ячейках B8-C8 "Телефон (111)222-32-32"

и т.п. контактная информация о клиенте, которую они листом могут распечатать с линиями и дополнительными надписями.

Причем, так как клиентов много, то таких листов в книге - сотни и загружается такая книга в память при открытии
по нескольку минут на вполне еще приличных компьютерах.Использование процессора подскакивает до 80%.
(Менеджеры недовольны и требуют компьютеров с новейшими мощнейшими игровыми процессорами).
И потом им еще надо побыстрее найти нужный лист клиента, когда они разговаривают по телефону, чтоб подредактировать.

Как-то напрашивается преобразование такой книги Excel в базу данных Access, которая бы загружалась быстрее,
где каждая строка была бы информацией о фирме, а в колонках - в поле1 - название фирмы, в поле2 - контактное лицо, в поле3 - телефон и т.д.

Как быстрее выполнить такое преобразование и прав ли я в том, что такой файл Access будет открываться быстро?
Я посмотрел мастер импорта в Access и что-то он не предлагает таких функций.

Таким образом, для того, чтобы перенести мастером в Access, первым делом я решил собрать всю информацию с листов в одну таблицу на одном листе1 Excel в той же книге.
Если просто копировать, то данные с листов не копируются нормально по колонкам, поэтому я копирую специальной вставкой ссылки.
Вручную тысячи фирм не перекопировать, надо автоматизировать.

Перебор листов действительно можно обойти применением оператора for each, спасибо.
Но у меня в макросе есть и перебор строк Range("PostNum").Select, где PostNum - текстовая переменная образующаяся сложением столбца А и строки 3,4,5,6,...,309
Видимо и тут нельзя использовать переменные и Range должны стоять только константы?

В идеале такая база данных клиентов должна представлять собой таблицу в несколько сот строк и шаблон для распечатки одного листа с информацией о клиенте и все.

alef2474, создайте в книге новый лист "Список", вставьте в модуль программу

Код:

Sub Create_list()

    i = 1

    For Each ws In Worksheets

        If ws.Name <> "Список" Then

            Sheets("Список").Cells(i, 1) = ws.Range("b1")

            Sheets("Список").Cells(i, 2) = ws.Range("a6")

            Sheets("Список").Cells(i, 3) = ws.Range("b8")

            i = i + 1

        End If

    Next

Sheets("Список").Activate

MsgBox "Формирование списка завершено"

End Sub

и запустите

Спасибо большое, все сработало как надо.(высота строк только резко подскочила в Список) Этим простым кодом Вы разрешили еще несколько вопросов, которые меня мучали.
Выбор в range только первой ячейки из объединенных для меня был не очевиден. В range действительно, наверно, могут ставиться только константы. В help'e (ниже) выделенный текст меня ввел в заблуждение.(A1-style подразумевает только константы)

Цитата:

The name of the range. This must be an A1-style reference in the language of the macro. It can include the range operator (a colon), the intersection operator (a space), or the union operator (a comma). It can also include dollar signs, but they’re ignored. You can use a local defined name in any part of the range. If you use a name, the name is assumed to be in the language of the macro.

alef2474, не совсем понятно ваше смущение. В приведенной вами цитате речь идет о том как передавать (определять) диапазон в макросах. И также говорится о том, что диапазон может быть назван на локальном (не английском) языке. Просто в этом случае при определении такого диапазона имя нужно заключать в кавычки, например

Код:

Range("Мой диапазон")

Цитата:

Цитата alef2474

Выбор в range только первой ячейки из объединенных »

а вы активируйте диапазон из выделенных ячеек и посмотрите, какое имя отображается в поле имен. Это и будет крайняя левая ячейка. Конечно, это справедливо, если вы не присвоили этому диапазону собственное имя.

Цитата:

Цитата okshef

что диапазон может быть назван на локальном (не английском) языке. »

Я слово local понял как не относящееся к языку, а как локальный идентификатор в этой локальной процедуре, а не внешней.А язык(language) macro как язык программирования (VBA). Чаще слово local именно так понимается.
Плохо, что в range нельзя использовать текстовые переменные.

Цитата:

Цитата okshef

а вы активируйте диапазон из выделенных ячеек и посмотрите, какое имя отображается в поле имен. Это и будет крайняя левая ячейка. »

Да, я обратил внимание, именно так. Но подумал, сперва, что в range надо подставлять диапазон. Подставил - и ничего не получилось.