Need help with a macro to auto copy information across sheets.

[farmertml]

Hi guys,

I have a spreadsheet with multiple sheets (about 50+) weighing in now at 30mb.

I have a summary sheet which needs to take information from the other sheets and put it in a table I've created. Each sheet with information which I need has the data in the same cells.

What I would like it to do is :-
1. Copy cells E:1010, F:1010 and H:1010 from every sheet in the workboot into my summary page, starting in cells D:5, E:5 and F:5 respectively.
2. If it's possible, take the name of the sheet it's copying from and place it in row B on the same line as the other three (I.E. in the case above, B:5).

I think it might involve a Vlookup or something similiar,
All help is greatly appreciated.

 

[Sandeep Warrier]

This should help.

Sub test()
 
    Dim i As Integer
    Dim wsname As String
    
    For i = 2 To ThisWorkbook.Sheets.Count
    
        Sheets(i).Select
        wsname = Sheets(i).Name
        Range("E1010:F10102,H1010").Copy
        Sheets(1).Select
        Range("D5").Select
        
        If ActiveCell.Value <> "" Then
        
            If ActiveCell.Offset(1, 0).Value <> "" Then
            
                ActiveCell.End(xlDown).Select
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
                
            Else
            
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
                
            End If
            
        Else
            
            ActiveSheet.Paste
            
        End If
        ActiveCell.Offset(0, -1).Value = wsname
        
    Next i
 
End Sub

 

[farmertml]

Hi there thank you for your response.
I ran your macro and it come up with a runtime error 1004. - That command cannot be used on multiple selections.

When debugging I found it was of the selection so I just changed the range to be E1010:H1010. Once running this it worked but I got a "Type Mismatch" error.

When debugging it was this value that returned the problem

If ActiveCell.Value <> "" Then

Any Ideas?
Thanks for your help

 

[Sandeep Warrier]

The runtime error 1004 was due to range definition..

Change Range("E1010:F10102,H1010").Copy to Range("E1010:F1010,H1010").Copy

For the other error, try changing ActiveCell to Selection. So ActiveCell.Value will become Selection.Value and so on.

The code ran perfectly when I tested it so I dunno why it is not doing the same for you.

 

[farmertml]

Aha OK.. I changed it from ActiveCell to Selection and that didn't work but I went back and removed that rouge 2 and it works perfectly.

Thank you sir you're most kind and have saved me ALOT of pain!

All the best

 

[Sandeep Warrier]

You are welcome.

I'm glad I was able to help.