copy single row from multiple worksheets into new worksheet

I'm not a developer, but was recently hired for a newly created position, meaning I'm trying to create reports and things from scratch that have never been done before. The IT department doesn't have time to teach me and so I'm trying to learn VBA and Access and other advanced data analysis tools, but I don't understand how to write code to the extent I need it yet. I used several things from these forums, but I've been lucky enough to mostly copy and paste to get what I need.

I copied this from this forum (second answer): copy the same row from multiple sheets into one sheet in excel

This is the code I copied:

Sub copyrow()
Dim Nrow As Long, Nsheet As Long
Dim i As Long

Nrow = 7
Nsheet = 6

For i = 1 To Nsheet - 1
Sheets(i).Cells(Nrow, 1).EntireRow.Copy Sheets(Nsheet).Cells(i, 1)
Next i
End Sub


I tested it and it worked, but I didn't realize that Nsheet meant it would go to the 6th sheet and replace the data there. So I began again and opened a blank worksheet at the front of the others. All I did was change Nrow to 6 because that's the row I want copied. Then I changed Nsheet to 1 so it would put the results on the first sheet. Once I made that Nsheet change, it wouldn't work. I don't understand the code enough to know what I did wrong.

Here's what my code looks like now:

Sub copyrow()
Dim Nrow As Long, Nsheet As Long
Dim i As Long

Nrow = 6
Nsheet = 1

For i = 1 To Nsheet - 1
Sheets(i).Cells(Nrow, 1).EntireRow.Copy Sheets(Nsheet).Cells(i, 1)
Next i
End Sub


But when I run it, nothing happens. I currently have the code pasted into the first worksheet. I thought maybe I should add the code to another worksheet but that gave me an error when trying to run. I don't know if that matters. I feel really stupid and am sure it's a minor petty thing, so feel free to roll your eyes.

Thanks!

2 Answers
2

Loop from the 2nd to the 6th sheet to gather data. Nsheet is now changed back to 6 and your loop increases i from 2 to 6. It works the same if you could remove the 'Nsheet' assignment and change your For loop to For i = 2 to 6.

For i = 2 to 6

If the number of sheets changes for each spreadsheet, you could use Sheets.Count instead of specifying 6.

Sheets.Count

6

In .Cells(i - 1, 1)... the -1 has been added because i is reused. i is used as a sheet reference when looping through sheets to gather data; here, it is reused as a row number variable. Sheet2 data will be added to row 1 of sheet1, sheet3 data will be added to row 2 of sheet1, etc. If there are headers on your sheet1, then -1 can be removed.

.Cells(i - 1, 1)

-1

i

i

-1

Sub copyrow()
Dim Nrow As Long, Nsheet As Long
Dim i As Long

Nrow = 7
Nsheet = 6

For i = 2 To Nsheet
Sheets(i).Cells(Nrow, 1).EntireRow.Copy Sheets(1).Cells(i - 1, 1)
Next i
End Sub


i

Sheets(i).Cells...

_{Dim Nrow As Long, Nsheet As Long
Dim i As Long}

You're on the right track here. Always use Long; never use Integer. Integers aren't cool and will invisibly break your code faster than anything else. Longs are cool and won't break your code for silly reasons.

_{... and opened a blank worksheet at the front of the others.}

Your code can do this easily.

worksheets.add(before:=worksheets(1)).name = "All Rows"


If you run this a second time, you will have to change the name or first remove the existing worksheet named 'All Rows'.

_{Nrow = 7
Nsheet = 6}

You want to copy row 6 from all other worksheets so NRow = 6. Nsheet can be used to hold the total number of worksheets in the workbook.

Nrow = 6
Nsheet = worksheets.count


_{For i = 1 To Nsheet - 1
    ...
Next i}

You created a new worksheet at the beginning of the queue so to address the other worksheets, start at 2. Loop through until you reach the total number of worksheets, i.e. Nsheet as was assigned above.

for i=2 to Nsheet
' <copying code goes here>
next i


_{Sheets(i).Cells(Nrow, 1).EntireRow.Copy Sheets(1).Cells(i - 1, 1)}

This should work 'as-is' now. I prefer to use the Worksheets collection instead of the Sheets collection since a Chart page, Macro sheet, etc is a member of Sheets but not of Worksheets. I also typically add the Destination parameter name since it makes the code easier to read. You might want to use column letters instead of the column ordinal index number for clarity.

Worksheets(i).Cells(Nrow, "A").EntireRow.Copy Destination:=Worksheets(1).Cells(i - 1, "A")


Put this altogether in a public module code sheet, not a worksheet's private code sheet. Private code sheets have their purpose, typically for code that primarily involves that worksheet only. This code is just better suited to a public module.

Sub copyrow()

Dim Nrow As Long, Nsheet As Long
Dim i As Long

worksheets.add(before:=worksheets(1)).name = "All Rows"

Nrow = 6 'row to copy
Nsheet = worksheets.count 'the count AFTER adding the destination worksheet

For i = 2 To Nsheet
Worksheets(i).Cells(Nrow, "A").EntireRow.Copy Destination:=Worksheets(1).Cells(i - 1, "A")
Next i

End Sub


Indent your code for readability.

Go into the VBE's Tools, Options and put a check beside Require variable declaration. This will put Option Explicit at the top of every new code sheet you create/insert. It will save you hours of grief from silly variable misspellings or omissions. It is also quite possibly the single biggest time saver that you will add to your coding experience.

Option Explicit

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.