Thread: macro to copy specific cells from multiple sheets

Hello all,

I need a macro to copy certain cells from many sheets into a new one. I am attaching a sample. what I want to do is copy cells "G5" "G6" "G18" from the sheets with the month names (not the main one) into a new one and put them in three different columns (all G5s, G6s, G18s).

Many thanks in advance

Hello Costasg,

This macro will create a worksheet called "Summary" and add the ranges G5:G6 and G18 from each monthly sheet into columns "A:C. This macro has been added to the attached workbook.

'Thread:  http://www.excelforum.com/excel-programming/783136-macro-to-copy-specific-cells-from-multiple-sheets.html
'Poster:  Costasg
'Written: July 06, 2011
'Author:  Leith Ross

Sub Summarize()

  Dim Cell As Range
  Dim Data(2) As Variant
  Dim R As Long
  Dim Rng As Range
  Dim RngEnd As Range
  Dim SumWks As Worksheet
  Dim Wks As Worksheet
  
   'Look for the Summary worksheet
    On Error Resume Next
      Set SumWks = Worksheets("Summary")
      If SumWks Is Nothing Then
        'Create the worksheet if it does not exist
         Set SumWks = Worksheets.Add(After:=Worksheets(Worksheets.Count))
         SumWks.Name = "Summary"
      End If
    On Error GoTo 0
    
   'Row 1 has column headers
    Set Rng = SumWks.Range("A2")
    Set RngEnd = SumWks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = SumWks.Range(Rng, RngEnd).Offset(1, 0)
    
    Application.ScreenUpdating = False
    
      For Each Wks In Worksheets
        Select Case LCase(Wks.Name)
          Case Is <> "main", "summary"
              Data(0) = Wks.Range("G5").Value
              Data(1) = Wks.Range("G6").Value
              Data(2) = Wks.Range("G18").Value
              Rng.Resize(1, 3).Offset(R, 0).Value = Data
              R = R + 1
          Case Else
              'Do nothing
        End Select
      Next Wks
      
    Application.ScreenUpdating = True
  
End Sub

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Wow Leith, that is very efficient. I posted without refreshing so I did not see your reply. Puts my mess of code to shame. Good Work!

Hello Thrux,

You code isn't really all that bad. You have the concept of what needs to be done and that's about 50 percent of the battle. The macro recorder is a stepping stone. The code it produces works but isn't refined.

As you do more coding, and visit the boards for ideas, your code will become better and better. None of us were born knowing all this. It takes time to learn it and then a little more time to refine it.

OK, this is awesome, Leith. I'm struggling with getting this to write to a new workbook instead of a new worksheet. When I add a piece of code to create a new workbook, that new workbook becomes the main workbook and I cann't get the summary to refer to the "old" workbook. Any suggestions?

Mcollins999,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.