You are here:

Excel/Loop through Sheets

Advertisement


Question
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.

Selwyn.

Answer
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

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Dave Morrison

Expertise

I can answer most excel questions My Website is davesexcel.com

Experience

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

Publications
My website davesexcel.com

Education/Credentials
Business admin. U of R.

Awards and Honors
Microsoft Excel MVP 2011-2012

©2016 About.com. All rights reserved.