copy one cell data from multiple sheet to master sheet-CodePudding

I know there is a code for copy data from cell to cell given the Origen is there a better way to copy the same cell from multiple sheets & skipping the master sheet Like sheet2 cell A1(april), sheet3 cell "A1"(Johny), Sheet4 cell "A1"(May) and go on to the next multiple sheets like that and past them in the master sheet

Sub Copy_Example()

  Range("A1").Copy Destination:=Range("B3")

End Sub

CodePudding user response：

Sub macro1()

   Dim wb As Workbook, ws As Worksheet, ar, i As Long
   
   Set wb = ThisWorkbook
   ReDim ar(1 To wb.Sheets.Count - 1, 1 To 1)
   
   i = 0
   For Each ws In wb.Sheets
        If LCase(ws.Name) <> "master" Then
            i = i   1
            ar(i, 1) = ws.Range("A1").Value2
        End If
   Next
   
   wb.Sheets("master").Range("A1").Resize(UBound(ar)) = ar

End Sub

CodePudding user response：

Yes, your code is great but It was given me an error then I Just fix a little now it's working

Sub macro1()

   Dim wb As Workbook, ws As Worksheet, ar, i As Long
   
   Set wb = ThisWorkbook
   ReDim ar(1 To wb.Sheets.Count, 1 To 1)
   
   
   For Each ws In wb.Sheets
        If ws.name <> "cover" Then
            i = i   1
            ar(i, 1) = ws.Range("D4").Value
        End If
   Next
   
   wb.Sheets("cover").Range("A1").Resize(i, 1).Value = ar

End Sub