Copy row from every sheet, into one sheet

[Yulyo]

Hi all,

Could you please help me with a VBA that copies row 500 from every sheet, to "Central" sheet, starting with A2?
I know it should be simple, but i just can't get it to work...

Thank in advance.

 

[JoeMo]

Sub CopyToCentral()
Const whichRow As Long = 500
Dim sht As Worksheet, nxrw As Long
nxrw = Sheets("Central").Range("A2").Row
For Each sht In Worksheets
    If sht.Name <> "Central" Then
        Intersect(sht.Rows(whichRow), sht.UsedRange).Copy
        Sheets("Central").Cells(nxrw, "A").PasteSpecial Paste:=xlAll
        nxrw = nxrw + 1
    End If
Next sht
Application.CutCopyMode = False
End Sub

 

[Logit]

.

Or :

Option Explicit


Sub test()
Dim a, sh As Worksheet, r As Range, rw&: rw = 0
For Each sh In Worksheets
  If Not sh.Name = "Central" Then
      Set r = sh.Range("A500").EntireRow
       a = r.Value: Sheets("Central").Range("A2").Offset(rw).Resize(UBound(a, 1), UBound(a, 2)).Value = a
      rw = rw + UBound(a, 1)
      a = ""
   End If
Next sh
End Sub

 

[Yulyo]

Thank you both for your answers.
Both solution are working just perfect, but i prefer Logit's one, since the original cells contain formulas and with JoeMo's solution i'm gettin' #REF error.
Yes, they should be paste as values, so not a big problem.

Again, thank you both.

 

[Logit]

.
You are welcome.