Copy/ Paste From Multiple Worksheets to One

[Mypcool]

Hello all,

I am really new to VBA and have really tried to do my research before posting this question.

I am trying to write a Macro to take a specific range from multiple worksheets within one workbook into one worksheet with all the ranges showing across the columns (or rows, I would like to do both as I may need the data one way or another) .

What i am attempting to do create the code to activate the first sheet, select the range, select the target sheet, look for the next empty column, paste the range, then loop back to second sheet, rinse and repeat.

The issue i am having is "look for the next empty column, paste the range"

I have attempted to run a macro within the macro just to sperate the tasks but no luck.

Here is the code

Sub Move_Over()
Dim c As Integer
Dim d


For c = 1 To 5


Sheets(c).Activate
Sheets(c).Range("H182:J290").Copy Destination:=Sheets("Sheet2").Range("A1")
  
'Copy the data
Sheets(c).Range("H182:J290").Copy


'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
' ?


' Run find empty macro
    Call find_blank
    
      
'Paste in the target destination
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


Application.CutCopyMode = False


Next c


End Sub


   
Sub find_blank()
'
' Test Macro
Dim ws As Worksheet


Set ws = ActiveSheet


For Each cell In ws.Columns(1).Cells
    If IsEmpty(cell) = True Then cell.Select:
Exit For
Next cell

I am really new to all this and appriciate all the help.

Thanks

 

[Fluff]

How about

Sub Mypcool()
   Dim i As Long, j As Long
   j = 1
   For i = 1 To 5
      Sheets("sheet2").Cells(1, j).Resize(109, 3).Value = Sheets(i).Range("H182:J290").Value
      j = j + 3
   Next i
End Sub

 

[Mypcool]

Works like a charm.

Thank you for your help!

 

[Fluff]

You're welcome & thanks for the feedback