How to automate copying from multiple spreadsheets into one summary one in the same workbook

[Andrew_A]

Hi there,

I have just started using VBA and realised how difficult it really is.


My problem is automating a process that takes information from a single column with a range of D7:D77 in 22 spreadsheets and places this information in a summary spreadsheet as rows corresponding to the names of each spreadsheet. This is within the same workbook.

For example say spreadsheet A1 has a column from D7:D77 I would like this information in a summary spreadsheet with the row titled A1 and D1:D77 transposed. This would then be repeated for the remaining spreadsheets

Any help would be great thanks

 

[rallcorn]

Welcome to the Mr. Excel message board.

Try:

Sub CopySheets()
'Assumptions:
'"Summary" sheet is the first sheet on left in file
'"Summary" sheet has headings in row 1
'"Summary" sheet column A will hold the sheet names
'"Summary" sheet column B is where the copied data will begin
 
Dim ShtName As String
Dim s As Long, sh As Long
Dim SumSht As Worksheet: Set SumSht = Sheets(1)
 
Application.ScreenUpdating = False
Application.DisplayAlerts = False
 
'Count sheets
sh = Worksheets.Count
 
'Loop through sheets beginning with 2nd sheet
For s = 2 To sh
    
    'Assigns name of current sheet in loop to variable
    ShtName = Sheets(s).Name
    
    'Populates the next available row in Col A
    'on SUMMARY SHEET with the name of the sheet
    'being copied
    SumSht.Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Value = ShtName
    
    'Copies current sheet in loop
    'You had conflicting info . . . twice you say D7:77
    'but in another place you say D1:D77 -- code uses
    'D7:D77
    Sheets(s).Range("D7:D77").Copy
    
    'Pastes copied data as values & transposed
    'onto the Summary sheet row next to the sheet name
    '(beginning in Col B)
    SumSht.Range("B" & Range("A" & Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True

    Application.CutCopyMode = False
 
Next s  'Move to next sheet & repeat steps
 
Application.ScreenUpdating = True
Application.DisplayAlerts = True
 
MsgBox "Done!"
 
End Sub

 

[Andrew_A]

Thanks Rallcorn, works like a dream.

Could you recommend any resources for learning VBA?

 

[rallcorn]

Glad it worked for you.

Having zero programming knowledge, learning VBA has been a slow learning curve for me (3+ years) . . . and still have so much to learn!!

I began with "VBA and Macros for Microsoft Excel" by Bill Jelen (Mr. Excel)/Tracy Syrstad - literally reading it through twice trying to grasp it before trying anything. Then, reading posts here, copying the code and trying to replicate the situation and stepping through the code to understand it. After that, trying to solve posts here (though usually someone else already posts a solution before I'm done with my code!! - but that is good, too, because I learn a lot from the approach of others.)

I will say though, learning VBA has really enhanced my value at work - in my "spare" time over the past year I have automated processes that have saved 5,300+ annual man hours with opportunity to multiply that many times over if I had the time. With the state of the economy, that's significant!!

See Hiker95's list below.
http://www.mrexcel.com/forum/excel-...os.html?highlight=list+resources+learning+vba

Good luck!

 

[Andrew_A]

Thanks again, much appreciated!