Kopējiet kolonnu vai kolonnas no katras darbgrāmatas mapē, izmantojot Microsoft Excel VBA

Anonim
  • Makro kopēs katra darblapas pirmās darblapas daļu, kas atrodas mapē C: \ Data, uz jūsu darbgrāmatas pirmo darblapu.
  • Pirmais makro veic parastu kopiju, bet otrs makro kopē vērtības.

Atcerieties, ka programmā Excel ir tikai 256 kolonnas

Sub CopyColumn () Dim bāzesgrāmata kā darbgrāmata Dim mybook kā darbgrāmata Dim sourceRange As Range Dim destrange As Range Dim cnum As Integer Dim i As Long Dim a As Integer Application.ScreenUpdating = False With Application.FileSearch .NewSearch .LookIn = "C: \ Dati ".SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute ()> 0 Tad Set basebook = ThisWorkbook cnum = 1 For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open (.FoundFiles (i)) Set sourceRange = mybook.Worksheets (1). Slejas ("A: B") a = sourceRange.Columns.Count Set destrange = basebook.Worksheets (1) .Cells (1, cnum) sourceRange.Copy destrange mybook.Close cnum = i * a + 1 Tālāk i beigas Ja beidzas ar lietojumprogrammu.ScreenUpdating = True End Sub Sub CopyColumnValues ​​() Dim bāzesgrāmata kā darbgrāmata Dim mybook kā darbgrāmata Dim avots Diapazons kā diapazons Dim destrange kā diapazons Dim cnum Kā vesels skaitlis Dim kā tik garš kā vesels skaitlisks pieteikums. ScreenUpdating = False ar Application.FileSearch .NewSearch .LookIn = "C: \ Data" .SearchSubFolders = False .FileType = msoFileTypeExcelWorkbooks If .Execute ()> 0 Tad Set basebook = ThisWorkbook cnum = 1 For i = 1 To .FoundFiles.Count Set mybook = Workbooks.Open (.FoundFiles (i)) Set sourceRange = mybook.Worksheets ( 1). Kolonnas ("A: B") a = sourceRange.Columns.Count With sourceRange Set destrange = basebook.Worksheets (1) .Columns (cnum). _ Mainīt izmērus (, .Columns.Count) Beigt ar destrange.Value = sourceRange.Value mybook.Close cnum = i * a + 1 Next i End if End with Application.ScreenUpdating = True End Sub