Looping cutting and pasting with vba on next avaialable row

Question

I'm new to vba but I think it's the best solution to my issue. I need to copy a specific range of data from multiple sheets L1:L7(400+) and paste it on a summary sheet L2:R2. I have to transpose the data when pasting it and each new set of data to be copied on the next available row. I have watched numerous videos and read articles but none of them seem to provide a complete solution.

When I tried to make a loop and try to get it to get to the next row, it doesn't work. It always seems to get stuck in the same "Range ("L2").PasteSpecial Transpose: True" place. It works alone from the "ActiveSheet.Select", so just for one sheet and for one row. Below is the last loop combination I tried.

Any help would be greatly appreciated!

Sub CopyData()
    
    Dim sheet As Worksheet
    
    For Each sheet In ThisWorkbook.Sheets
        If sheet.Name <> "Summary" Then
            ActiveSheet.Select
            Range("L1:L7").Copy
            Sheets("Summary").Select
            Range("L2").PasteSpecial Transpose:=True
        End If
    Next
End Sub

Don't use `ActiveSheet`, use `sheet`, and [avoid `Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) too. — BigBen, May 03 '21 at 20:50
[How to find the next available row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, May 03 '21 at 20:51

score 0 · Accepted Answer · answered May 03 '21 at 21:23

0

As @BigBen has commented, your issues about having it always paste the same data on L2 stem from:

Ignoring the looping sheet and working only on the ActiveSheet
Hard coding a cell reference and not using a dynamic LastRow reference

Here is what it would look like with those two items corrected:

Sub CopyData()
    
    Dim sheet As Worksheet, nextRng As Range
    
    For Each sheet In ThisWorkbook.Sheets
        If sheet.Name <> "Summary" Then
            sheet.Range("L1:L7").Copy
            
            'Finding the next open range to paste the data
            With Sheets("Summary")
                Set nextRng = .Range("L" & .Rows(.UsedRange.Rows.Count + .UsedRange.Row).Row)
            End With
            
            nextRng.PasteSpecial Transpose:=True
        End If
    Next

End Sub

Alternative NextRng formula:

With Sheets("Summary")
    Set nextRng = .Cells(.Cells(.Rows.Count, 12).End(xlUp).Row + 1, 12)
End With

answered May 03 '21 at 21:23

Toddleson

4,321
1
6
26

Your alternative NextRng formula could simply `Set nextRng = .Cells(.Rows.Count, 12).End(xlUp).Offset(1, 0)` – chris neilsen May 03 '21 at 21:57
Thank you both so much. @Toddleson, this is life-saving, my vlookup/Indirect formula couldn't work for this as it depended on other workbooks, way too complex and I then started looking at macros. I can't thank you enough! I know I can check the internet for online courses to learn VBA, but do you recommend any in particular? Many thanks again. Wherever you are, may you have a great day! – Nessy May 03 '21 at 22:01
@Nessy When I started with VBA, I learned a lot by using the built-in macro recorder tool in excel. After that, I started looking up how others write their code and copying techniques and conventions. Now, I mostly look at the MS Docs and search up discussions on this website. – Toddleson May 04 '21 at 13:11
@Toddleson, brilliant! Thanks for the tips! – Nessy May 06 '21 at 20:01

Looping cutting and pasting with vba on next avaialable row

1 Answers1