jeudi 21 avril 2016

Excel VBA Cycle For

[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