You are here:

Excel/Loop through Sheets


Dear dave,
I want a small amendment in the code you previously wrote for me.This code copies the last row of existing sheets and pastes in the summary sheet in the same workbook.It copies all the columns of the last row and pastes it.Is there a way for copying only columns B and D from each sheet with header at row 2 instead of the entire last row?

Sub LoopThroughSheets()
  Dim shts As Worksheet
  Dim Ws As Worksheet
  Set Ws = Worksheets("Summary")
  For Each shts In Sheets
      If shts.Name <> Ws.Name Then
        With shts
        .Cells(Rows.Count, "A").End(xlUp).EntireRow.Copy _
        Ws.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        End With
      End If
  Next shts
End Sub

Thanks a lot.


Try this,

Sub Button1_Click()
   Dim shts As Worksheet
   Dim Ws As Worksheet, wsRws As Long
   Dim Rws As Long, Rng1 As Range, Rng2 As Range

   Rws = Cells(Rows.Count, "A").End(xlUp).Row

   Set Rng = Range(Cells(1, 1), Cells(Rws, 1))
   Set Ws = Worksheets("Summary")
   For Each shts In Sheets
       If shts.Name <> Ws.Name Then
           With shts
               Rws = .Cells(Rows.Count, "B").End(xlUp).Row
               wsRws = Ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
               Set Rng1 = .Cells(Rws, 2)
               Set Rng2 = .Cells(Rws, 4)
           End With
           Rng1.Copy Ws.Cells(wsRws, 1)    'or where ever you want to put it
           Rng2.Copy Ws.Cells(wsRws, 2)

       End If
   Next shts
End Sub

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Dave Morrison


I can answer most excel questions My Website is


I have been working with spreadsheets since 1991. I am a self-trained excel developer since 2005.

My website

Business admin. U of R.

Awards and Honors
Microsoft Excel MVP 2011-2012

©2017 All rights reserved.