Copy row from every sheet, into one sheet

Yulyo

Board Regular
Joined
Jul 17, 2017
Messages
94
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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Code:
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
 
Upvote 0
.

Or :

Code:
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
 
Upvote 0
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.

 
Upvote 0

Forum statistics

Threads
1,224,884
Messages
6,181,555
Members
453,053
Latest member
Kiranm13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top