Используя excel VBA, как вы копируете несколько строк одного значения в новый лист? Vba excel создать лист

vba - Как создать экземпляры скрытого рабочего листа в excel с помощью vba?

Я создаю Excel с поддержкой макросов в качестве инструмента для создания "create table" sql script. Лист создается там, где нужно вводить имена столбцов, тип данных и т.д., И при нажатии кнопки script будет сгенерирован. Этот лист называется "Script Генератор". Теперь мне нужен лист "Index", который будет иметь имена таблиц и кнопку. Когда я нажимаю кнопку, мне нужно открыть листы "Script generator" для каждого имени таблицы, и эти листы должны быть переименованы в имя таблицы.

Код индексной строки выглядит следующим образом:

Sub Add_sheets() On Error Resume Next Dim count As Integer Dim r As Range Dim sh As Worksheet For Each r In Range("A3:A103") If Not r = "" Then count = 0 For Each sh In ActiveWorkbook.Sheets If sh.Name = r.Value Then count = count + 1 End If Next sh If count = 0 Then With ActiveWorkbook.Sheets .Add(after:=Worksheets(Worksheets.count), Type:="C:\Macro\Script_Template.xltm").Name = r.Value End With ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", _ SubAddress:=Sheets(r.Value).Name & "!A1" End If End If Next r End Sub

Теперь проблема заключается в том, что я добавляю генератор script, сохраненный как "Script_Template.xltm" извне. Мне нужен только один Excel, который сделает все это. Значит, файл индекса должен внутренне открыть/добавить новые листы формата 'Script generator', чтобы он сформировал один полный инструмент. Возможно, сокрыв этот лист и вызвав его экземпляры с помощью макросов и переименовав эти листы. Как это сделать через VBA? Может ли кто-нибудь помочь мне с этим?

задан Appy 26 июня '12 в 7:24 источник поделиться

qaru.site

VBA Excel. Рабочий лист (обращение, переименование, скрытие)

Обращение к рабочим листам Excel из кода VBA. Переименование листов, скрытие и отображение с помощью кода VBA Excel. Свойства Worksheets.Name и Worksheets.Visible.

Обращение к рабочим листам
Переименование листов

Скрытие и отображение листов

Обращение к рабочим листам

Рабочий лист (Worksheet) принадлежит коллекции всех рабочих листов (Worksheets) книги Excel. Обратиться к листу можно как к элементу коллекции и, напрямую, по его уникальному имени.

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

Обращение к рабочему листу в коде VBA Excel:

'По уникальному имени УникИмяЛиста 'По индексу Worksheets(N) 'По имени листа на ярлычке Worksheets("Имя листа")

УникИмяЛиста - уникальное имя листа, отображаемое в проводнике редактора VBA без скобок, с помощью кода VBA изменить его невозможно.
N - индекс листа от 1 до количества всех листов в книге, соответствует порядковому номеру ярлычка этого листа в открытой книге Excel.

Имя листа - имя листа, отображаемое в проводнике редактора VBA в скобках, с помощью кода VBA изменить его можно.

Количество листов в рабочей книге Excel определяется так:

'В активной книге Worksheets.Count 'В любой открытой книге, 'например, в «Книга1.xlsm» Workbooks("Книга1.xlsm").Worksheets.Count

Переименование листов

В VBA Excel есть некоторые особенности в наименовании листов, так как у рабочего листа есть два свойства, связанных с именем: (Name) и Name. Откройте окно «Properties» в редакторе VBA, нажав клавишу «F4», и выделите любой лист в проводнике. Вы увидите, что в окне «Properties» свойству (Name) в скобках соответствует в проводнике уникальное имя листа без скобок, а свойству Name без скобок соответствует изменяемое имя листа в скобках. Оба имени в окне «Properties» можно редактировать.

С помощью кода VBA Excel можно редактировать только имя листа Name, отображаемое на ярлычке листа и в проводнике без скобок. Для этого используется свойство рабочего листа Worksheets.Name со следующим синтаксисом:

expression.Name

где expression - переменная, представляющая собой объект Worksheet. Смена имени осуществляется путем присвоения нового значения свойству Worksheets.Name.

Допустим, у нас есть лист с уникальным именем (Name) - Лист1, индексом - 1 и именем Name - МойЛист, которое необходимо заменить на имя - Реестр.

Лист1.Name = "Реестр" Worksheets(1).Name = "Реестр" Worksheets("МойЛист").Name = "Реестр"

Скрытие и отображение листов

Для скрытия и отображения рабочих листов в VBA Excel используется свойство Worksheet.Visible со следующим синтаксисом:

expression.Visible

где expression - переменная, представляющая собой объект Worksheet. Свойству Worksheet.Visible могут присваиваться следующие значения:

False - лист становится невидимым, но он будет присутствовать в списке скрытых листов, и пользователь сможет его отобразить с помощью инструментов рабочей книги Excel.
xlVeryHidden - лист становится супер невидимым и его не будет в списке скрытых листов, пользователь не сможет его отобразить. Актуально для Excel 2003-2016.
True - лист становится видимым.

Аналоги присваиваемых значений:

False = xlHidden = xlSheetHidden = 1
xlVeryHidden = xlSheetVeryHidden = 2
True = xlSheetVisible = -1 (константа xlVisible вызывает ошибку)

Примеры:

Лист1.Visible = xlSheetHidden Лист2.Visible = -1 Worksheets(Worksheets.Count).Visible = xlVeryHidden Worksheets("МойЛист").Visible = True

Как создать, скопировать, переместить или удалить рабочий лист с помощью кода VBA Excel, смотрите в этой статье.

vremya-ne-zhdet.ru

Как создать экземпляры скрытого рабочего листа в excel с помощью vba? MS Excel онлайн

Я создаю excel как макрос, как инструмент для создания sql-скрипта create table. лист создается там, где нужно вводить имена столбцов, тип данных и т. д., и при нажатии кнопки скрипт будет сгенерирован. Этот лист называется «Генератор сценариев». Теперь мне нужен лист «Index», который будет иметь имена таблиц и кнопку. Когда я нажимаю кнопку, мне нужно открыть листы сценария для каждого имени таблицы, и эти листы должны быть переименованы в имя таблицы.

код индексной таблицы выглядит следующим образом:

Теперь проблема заключается в том, что я добавляю генератор сценариев, сохраненный как «Script_Template.xltm» извне. Мне нужно только одно преимущество, которое сделает все это. Значит, файл индекса должен внутренне открывать / добавлять новые листы формата «генератор сценариев», чтобы он составлял один полный инструмент. Может быть, путем выделения этого листа и вызова его экземпляров через macos и переименования этих листов. Как это сделать через VBA? Было бы здорово, если бы кто-то помог мне с этим .. спасибо

Solutions Collecting From Web of "Как создать экземпляры скрытого рабочего листа в excel с помощью vba?"

Вы можете создать лист «Script_Template» и скрыть его, а затем использовать этот код, создать копию

Sheets("Script_Template").Visible = True Sheets("Script_Template").Copy After:=Sheets(ThisWorkbook.Sheets.count) ActiveSheet.Name = r.Value Sheets("Script_Template").Visible = False

Использование True и False для установки свойства Visible на листе не является хорошей практикой. Вы должны использовать уже предоставленные константы – xlSheetHidden, xlSheetVeryHidden и xlSheetVisible.

xlSheetVisible сделает ваш лист видимым, а xlSheetHidden скроет ваш лист. Установка его в xlSheetVeryHidden гарантирует, что единственный способ сделать видимый лист – через VBA, а не через меню Excel. Формат -> Лист -> Показать.

Применение:

Sheets("Script_Template").Visible = xlSheetVisible

excel.bilee.com

Используя excel VBA, как вы копируете несколько строк одного значения в новый лист?

Исходная информация

Вот два листа, с которыми я работаю:

Лист A (столбец AP): – Loc_ID и его информация

Loc_ID Loc_Name Emp_ID First Last 123456 ABCX - Sales Park 0012 Joe Schmo 123456 ABCX - Sales Park 0019 John Smith 123456 ABCX - Sales Park 0089 Gary Hammond 654321 ABCX - Sales Force 0192 Tom Lenardo 654321 ABCX - Sales Force 0165 Tim Hamilton

Sheet B (Columns AZ): – Акронимы, которые идут с каждым Loc_ID из листа A

ABC CBA ZAH XYZ 123456 532453 453853 366542 654321 123875 483286 546435 568723 K45524 214354

Моя цель состояла в том, чтобы выполнить взаимосвязь между двумя листами и иметь возможность добавлять в новый лист, который переименован в аббревиатуру (т. Е. ABC), и захватить Loc_ID из SHEET A, которые относятся к тому аббревиатуре, как показано в SHEET B, в новый лист: ABC.

Я выполнил свою задачу, но проблема заключается в том, что добавляется только одна строка из указанных мест.

Например, только эта строка отображается на новом листе: «ABC»

123456 ABCX - Sales Park 0012 Joe Schmo

Есть ли способ добавить несколько строк одного и того же Loc_ID, которые относятся к сокращению?

Код:

Sub Macro5() Dim shtA As Worksheet 'variable represents Sheet A Dim shtB As Worksheet 'variable represents Sheet B Dim shtNew As Worksheet 'variable to hold the "new" sheet for each acronym Dim acronyms As Range 'range to define the list of acronyms Dim cl As Range 'cell iterator for each acronmym Dim r As Integer 'iterator, counts the number of locatiosn in each acronym 'Dim valueToFind As String 'holds the location that we're trying to Find 'Dim foundRange As Range 'the result of the .Find() method '## Assign our worksheets variables Set shtA = Worksheets("Leavers") Set shtB = Worksheets("Tables") '## Assign the list of acronmys in Sheet B Set acronyms = shtB.Range("B1:Z1") '## Begin our loop over each acronym: For Each cl In acronyms.Cells '## Add a new sheet for each acronym: Set shtNew = Sheets.Add(After:=Sheets(Sheets.Count)) shtNew.Name = cl.Value r = 1 'start each acronym at "1" '## Loop over each row, which contain the location IDs ' assumes that there is no additional data below the location IDs ' this terminates at the first empty cell in each column Do While Not cl.Offset(r).Value = "" '## Define the value we're looking for: valueToFind = cl.Offset(r).Value 'Search in "SHEET A", Column A With shtA.Range("A:A") '## Assign the result of the Find method to a range variable: Set foundRange = .Find(What:=valueToFind, _ After:=.Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) End With '## Make sure the value was found: If foundRange Is Nothing Then MsgBox valueToFind & " not found!" Else: '## Resize the foundRange to include columns A:P Set foundRange = foundRange.Resize(1, 16) '## Copy & paste to the new worksheet for this acronym foundRange.Copy Destination:=shtNew.Cells(r, 1) r = r + 1 End If Loop Next End Sub

Solutions Collecting From Web of "Используя excel VBA, как вы копируете несколько строк одного значения в новый лист?"

Я думаю, здесь могут быть пара вопросов. Первый – в вашем заявлении о размере. Если я правильно понимаю, вы изменяете размер, чтобы всегда перезаписывать первую строку. Если вы включаете одну и ту же переменную, вы должны писать в другой диапазон каждый цикл. Можете ли вы попробовать что-то вроде этого?

Else: '## Resize the foundRange to include columns A:P Set foundRange = foundRange.Resize(r, 16) /*change the 1st parameter*/ '## Copy & paste to the new worksheet for this acronym foundRange.Copy Destination:=shtNew.Cells(r, 1) r = r + 1 End If

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

Set acronyms = shtB.Range("B:Z")

Знакомы с SQL?

Откройте рабочий лист ABC и добавьте Microsoft Query (лента данных, внешние данные)

Пример SQL для листа ABC:

SELECT * FROM [S1$] AS S1 WHERE S1.Loc_ID in (SELECT ABC FROM [S2$])

Вы также можете использовать мою надстройку SQL для Excel: http://www.analystcave.com/excel-tools/excel-sql-add-in-free/ . На основе SQL выше просто замените ABC на другие имена столбцов для других рабочих листов. Это займет 1 мин. Вершин 🙂

excel.bilee.com

создание списка подпапок и файлов в исходной папке MS Excel онлайн

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

Sub file_list() Call ListFilesInFolder("W:\ISO 9001\INTEGRATED_PLANNING\", True) End Sub Sub ListFilesInFolder(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean) Dim FSO As Object Dim SourceFolder As Object Dim SubFolder As Object Dim FileItem As Object Dim r As Long Set FSO = CreateObject("Scripting.FileSystemObject") Set SourceFolder = FSO.getFolder(SourceFolderName) r = Range("A65536").End(xlUp).Row + 1 For Each FileItem In SourceFolder.Files Cells(r, 1).Formula = FileItem.Name r = r + 1 X = SourceFolder.Path Next FileItem If IncludeSubfolders Then For Each SubFolder In SourceFolder.Subfolders ListFilesInFolder SubFolder.Path, True Next SubFolder End If Set FileItem = Nothing Set SourceFolder = Nothing Set FSO = Nothing End Sub Function GetFileOwner(ByVal FilePath As String, ByVal FileName As String) Dim objFolder As Object Dim objFolderItem As Object Dim objShell As Object FileName = StrConv(FileName, vbUnicode) FilePath = StrConv(FilePath, vbUnicode) Set objShell = CreateObject("Shell.Application") Set objFolder = objShell.Namespace(StrConv(FilePath, vbFromUnicode)) If Not objFolder Is Nothing Then Set objFolderItem = objFolder.ParseName(StrConv(FileName, vbFromUnicode)) End If If Not objFolderItem Is Nothing Then GetFileOwner = objFolder.GetDetailsOf(objFolderItem, 8) Else GetFileOwner = "" End If Set objShell = Nothing Set objFolder = Nothing Set objFolderItem = Nothing End Function

Я бы очень хотел это увидеть;

Столбец A = Хост-папка / подпапка

Столбец B = Имя файла

Столбец C = гиперссылка на файл

Это возможно?

У меня есть код, который создал гиперссылки, но я не знаю, как добавить к существующему коду.

Sub startIt() Dim FileSystem As Object Dim HostFolder As String HostFolder = "W:\ISO 9001\INTEGRATED_PLANNING\" Set FileSystem = CreateObject("Scripting.FileSystemObject") DoFolder FileSystem.GetFolder(HostFolder) End Sub Sub DoFolder(Folder) Dim SubFolder For Each SubFolder In Folder.Subfolders DoFolder SubFolder Next i = Cells(Rows.Count, 1).End(xlUp).Row + 1 Dim File For Each File In Folder.Files ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 1), Address:= _ File.Path, TextToDisplay:=File.Name i = i + 1 Next End Sub

Solutions Collecting From Web of "Excel VBA: создание списка подпапок и файлов в исходной папке"

Вы можете увидеть список свойств, поддерживаемых здесь File объектом: https://msdn.microsoft.com/en-us/library/1ft05taf(v=vs.84).aspx

Таким образом, вы можете улучшить свой код, где он принимает свойство .Name и помещает его в формулу ячейки, чтобы сделать что-то подобное с другими свойствами, такими как .Type файла.

For Each FileItem In SourceFolder.Files Cells(r, 1).Formula = FileItem.Name Cells(r, 2).Value = FileItem.Type ActiveSheet.Hyperlinks.Add Anchor:=Cells(r, 3), Address:= _ FileItem.Path, TextToDisplay:=FileItem.Name r = r + 1 X = SourceFolder.Path Next FileItem

nb Я использовал Value вместо формулы, но в этом случае результат будет таким же.

Аналогичным образом вы можете добавить еще одну Cells(r, 3).Value = line, чтобы установить значение ячейки в текущей строке r и столбце 3 независимо от вашей гиперссылки.

Я написал небольшой сценарий для этой цели моему коллеге еще некоторое время назад …

Смотрите мой код ниже:

Sub FolderNames() 'Written by Daniel Elmnas Last update 2016-02-17 Application.ScreenUpdating = False Dim xPath As String Dim xWs As Worksheet Dim fso As Object, j As Long, folder1 As Object With Application.FileDialog(msoFileDialogFolderPicker) .Title = "Choose the folder" .Show End With On Error Resume Next xPath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\" Application.Workbooks.Add Set xWs = Application.ActiveSheet xWs.Cells(1, 1).Value = xPath xWs.Cells(2, 1).Resize(1, 5).Value = Array("Subfolder", "Hostfolder", "Filename", "Date Created", "Date Last Modified") Set fso = CreateObject("Scripting.FileSystemObject") Set folder1 = fso.getFolder(xPath) getSubFolder folder1 xWs.Cells(2, 1).Resize(1, 5).Interior.Color = 65535 xWs.Cells(2, 1).Resize(1, 5).EntireColumn.AutoFit Application.ScreenUpdating = True End Sub Sub getSubFolder(ByRef prntfld As Object) Dim SubFolder As Object Dim subfld As Object Dim xRow As Long For Each SubFolder In prntfld.SubFolders xRow = Range("A1").End(xlDown).Row + 1 Cells(xRow, 1).Resize(1, 5).Value = Array(SubFolder.Path, Left(SubFolder.Path, InStrRev(SubFolder.Path, "\")), SubFolder.Name, SubFolder.DateCreated, SubFolder.DateLastModified) Next SubFolder For Each subfld In prntfld.SubFolders getSubFolder subfld Next subfld End Sub

Вот результат:

excel.bilee.com

Попытка создать макрос excel vba, который открывает новый лист, переименовывает его в соответствии со значением относительной ячейки в исходном листе

У меня есть основной список контактов. Я пытаюсь создать макрос, который использует относительную контрольную точку:

Откройте конкретный шаблон листа. Дайте ему имя = значение ActiveCell или первой ячейки, активированной в макросе, и скопируйте и вставьте информацию из главного списка на новый лист.

Я могу понять, как открыть лист и сделать копию и вставить, но я всегда получаю сообщение об ошибке при переименовании листа.

ActiveCell.Range("A1,A2:B26").Select ActiveCell.Offset(1, 0).Range("A1").Activate ActiveWindow.ScrollRow = 5 ActiveWindow.ScrollRow = 4 ActiveWindow.ScrollRow = 3 ActiveWindow.ScrollRow = 2 ActiveWindow.ScrollRow = 1 ActiveCell.Offset(-1, 0).Range("A1").Select Sheets("Patient List").Select Sheets.Add Type:= _ "C:\Users\Valerie\AppData\Roaming\Microsoft\Templates\Patient-History-Template1.xltx" Sheets("Patient List").Select Selection.Copy Sheets("Patient List").Select Sheets("Patient List").Name = "Patient List" Sheets("Patient 1").Select

Ниже здесь я хотел бы, чтобы имя нового листа = относительное значение первой ячейки, активированной в макросе, вместо «Джонс». Таким образом, я могу запустить макрос и получить отдельные листы для каждого имени в столбце А.

Sheets("Patient 1").Name = "Jones" Sheets("Jones").Select ActiveSheet.Paste Sheets("Patient List").Select ActiveCell.Offset(0, 1).Range("A1").Select Application.CutCopyMode = False Selection.Copy Sheets("Jones").Select ActiveCell.Offset(0, 1).Range("A1").Select ActiveSheet.Paste Sheets("Patient List").Select ActiveCell.Offset(0, 1).Range("A1").Select Application.CutCopyMode = False Selection.Copy Sheets("Jones").Select ActiveCell.Offset(2, -1).Range("A1").Select ActiveSheet.Paste Sheets("Patient List").Select

excel.bilee.com