CODEWITHSUNDEEP
excelvba
jules325
jules325

Reputation: 179

Copy/Paste Loop through worksheets to consolidate

I have a workbook which has approximately 300 worksheets. I'm trying to loop through each sheet, copy a specific range, and paste it on summary sheet. I need each successive paste to be put one row below the last used row. I'm newer to loops, but I think the amount of information being copied is excessive(causing an error), and I have come to understand that the .value method is much less memory intensive.

How do I incorporate the .value method to accomplish this? Here is the code I tried to write (again, I'm new to coding,sorry). 

Sub Consolidation()

Dim ws As Worksheet

Sheets("Summary").Select

For Each ws InThisWorkbook.Sheets

ActiveSheet.Range("A" & Rows.Count).End(xLUp).Offset(1).Value ="ws.Range("BB1").End(xLToRight).End(xLDown).Select"

Next ws

End Sub

Alternatively, is there a better way to do this? Meaning, is the .value method the way to tackle this issue?

Upvotes: 0

Views: 2834

Answers (2)

Jagan Keshavan
Jagan Keshavan

Reputation: 1

Sub loop_through_all_worksheets_cpyPst()

Dim ws As Worksheet
Dim starting_ws As Worksheet
Set starting_ws = Sheets("startAtSheet")

'remember which worksheet is active in the beginning


For Each ws In ThisWorkbook.Worksheets
    ws.Activate

If Not ws.Name = "SheettoPasteTo_skipCopy" Then 'This will skip Summary.
           
    Range("A2:I2").Select
'my section range had only columns till i - edit the to last column 

    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("SheettoPasteTo_skipcopy").Select
'edit this sheet name to copy to 

    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.End(xlDown).Select
   
End If
       
    ws.Cells(1, 1) = 1 'this sets cell A1 of each sheet to "1"
   
Next

starting_ws.Activate 
'activate the worksheet that was originally active

End Sub

Upvotes: 0

user8753746
user8753746

Reputation:

This question is similar than:

Simple Copy/Paste Loop not working on each worksheet

This is the way you can use the .Value

Sub WsLoop()

    Dim ws As Worksheet
    Dim Summary As Worksheet

    Set Summary = ThisWorkbook.Sheets("Summary")

    For Each ws In ThisWorkbook.Sheets
        If Not ws.Name = "Summary" Then 'This will skip Summary.
            Summary.Range("A1").Value = ws.Range("A1").Value
        End If
    Next ws

End Sub

Upvotes: 2

Related Questions