none
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
    ThisWorkbook.Activate
    Range("C4").Select
    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 ..."):

    Workbooks(DataFile).Worksheets(i).Activate


    Ashidacchi

    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,

    Terry

    Friday, September 8, 2017 9:51 AM