1

I am trying to write a macro using VBA where I go through each worksheet and copy the data into one main sheet (Sheet7).

But there are multiple sheets where the data are in the same ranges and I don't want the data to overlap in the main sheet. So I am trying to create a macro where the copied data moves down a row as the code runs through each sheet. My code so far is not working. Here's what I have so far.

Sub copyrecursive()

Dim ws As Worksheet    
Dim R As Range

For Each ws In ActiveWorkbook.Worksheets

    For Each R In Range("A1:E2").Rows               
        ws.Range("A1:E1").copy                
        Worksheets("Sheet7").R.PasteSpecial           
    Next R

Next ws    

End Sub

So I want the range A1:E1 in Sheet1 to copy to Sheet7 A1:E1 and then I want the range A1:E1 in Sheet2 to copy to Sheet7 A2:E2. Hope this is a good enough explanation. Thank you for any help.

0
  1. you need to qualify which worksheet you are working on

  2. you need to keep a row counter for the row you will paste the data on

  3. no need to copy, you can assign the values directly

  4. you need to check the worksheet name to not copy A1:E1 of sheet7

    Sub copyrecursive()
    
    Dim ws As Worksheet
    Dim R As Range
    Dim i as long
    
    i = 1
    For Each ws In ActiveWorkbook.Worksheets
        if ws.name <> "Sheet7" then
            Worksheets("Sheet7").Range("A" & i & ":E" & i).value = ws.Range("A1:E1").value
            i = i + 1
        end if
    Next ws
    
    End Sub
    
  • When I run this code in the line i + i + 1 turns into i i + 1 and I get an "Expected sub, function, or property error". Any thoughts? Thanks – user1063981 Jul 17 at 20:01
  • That was a typo, see my edit: i = i + 1 – cybernetic.nomad Jul 17 at 20:24
  • Yes I figured this and made the switch. Now that the code work however it produces no results on Sheet7. – user1063981 Jul 17 at 20:25
  • Works fine on my machine – cybernetic.nomad Jul 17 at 20:28
  • Yeah it works now. For some reason just retyping it all out worked. Thank you for your help. – user1063981 Jul 17 at 20:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.