VBA code to use a for loop across all sheets in a workbook to select the variable contents? RRS feed

  • Question

  • I have a workbook with multiple sheets, in each sheet I need to copy the same row contents to my master list. I have a code to get one cell value (which is N7) in each sheet to my master,

    The problem is, in some sheets, the cell value to be get into master will be one cell , In other sheets, it will be two or more cells like (N7 TO N11)

    How should I get this in to my master? My current code is,

    Dim DataFile As String
    Workbooks.Open Filename:=Range("T3").Value
    DataFile = ActiveWorkbook.Name
    For i = 1 To Workbooks(DataFile).Worksheets.Count
    ActiveCell.Value = Workbooks(DataFile).Worksheets(i).Range("N7").Value
    ActiveCell.Offset(1, 0).Select
    Next i

    Please help me on this.

    Thursday, September 7, 2017 11:35 AM

All replies

  • You need to add a line (just under "for i = 1 to ..."):



    Thursday, September 7, 2017 12:29 PM
  • Hi DivyaS55,

    You need to know which sheet need copy one cell and which sheet need copy multiple cells.

    Then you could choose different ways to copy cells.

    Here is an example and you need adjust it for your need.

    For i = 1 To Workbooks(DataFile).Worksheets.Count
    If Workbooks(DataFile).Worksheets(i).Name = "Sheet1" Then
    ActiveCell.Value = Workbooks(DataFile).Worksheets(i).Range("N7").Value
    ActiveCell.Offset(1, 0).Select
    ElseIf Workbooks(DataFile).Worksheets(i).Name = "Sheet2" Then
    Workbooks(DataFile).Worksheets(i).Range("N7:N11").Copy ActiveCell
    Count = Workbooks(DataFile).Worksheets(i).Range("N7:N11").Cells.Count
    ActiveCell.Offset(Count, 0).Select
    End If
    Next i

    Best Regards,


    Friday, September 8, 2017 9:51 AM