[Excel-VBA] I have a file named "vegetables_fruits" and 4 other files : "apple", "banana", "tomato", "cucumber". In the file "vegetables_fruits" I have a Worksheet named "List" where I fold the names of all 4 files (ex., cell "A2" = "apple", cell "A3" = "banana", cell "A4" = "tomato", cell "A5" = "cucumber"). In addition to the sheet "List" I have sheets "banana", "tomato" and "cucumber", but I don't have "apple". (it's normal) It's necessary to paste the column A from each of this 4 files to every sheet in the "vegetables_fruits" (ex., from file "apple" it's necessary to copy column A to file "vegetables_fruits" to sheet "banane" ; from file "banana" it's necessary to copy column A to file "vegetables_fruits" to sheet "tomato" etc.) Thank you very much for your help! P.S. It needs to create a For, but I don't know how I can to decribe all of this condition.
Sub CopyPaste()
Dim r As Variant
Dim a As Variant
Dim b As Integer
Dim nbcells As Integer
Dim ws As Worksheet
Worksheets("List").Activate
nbcells = Application.WorksheetFunction.CountA(Range("A2:A" & Range("A65536").End(xlUp).Row))
' === Creating a new sheet ===
For r = 2 To nbcells
Sheets.Add After:=Sheets(Sheets.Count - 1)
Worksheets(r).Name = Worksheets("List").Cells(r + 1, 1).Value
Next r
' === DATA ===
For Each ws In Sheets 'ActiveWorkbook.Worksheets
If ws.Name Like "*.xls*" Then
For a = 2 To nbcells
Windows(a).Activate
Range("B:B").SpecialCells(2).Copy
Workbooks("JiraKPI.xlsm").Activate
b = a + 1
If ws.Name = Worksheets("List").Cells(b, 1).Value Then
ws.Select
Range("A2").Select
ActiveSheet.Paste
End If
Next a
End If
Next
End Sub
Aucun commentaire:
Enregistrer un commentaire