Copy rows to a new worksheet if column a contains a number.

vac

Board Regular
Joined
May 21, 2002
Messages
211
Hi all,
I used to be able to do this, but have forgotten how. I need to
Copy rows from sheets 2 3 4 5 etc to worksheet 1, if column A on any of the worksheets contains a number.
I think last time I used the vlookup.
Can anyone help please.
Many Thanks,
Vac
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

i can;t see how you would have used vlookup, how about a macro?

Code:
Sub COPT_TO_SHT_1()
Application.ScreenUpdating = False
For MY_WBOOKS = 2 To 5
    Sheets(MY_WBOOKS).Activate
    For MY_ROWS = 1 To Range("A65536").End(xlUp).Row
        If IsNumeric(Range("A" & MY_ROWS)) Then
            Rows(MY_ROWS).Copy
            Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlValues)
        End If
    Next MY_ROWS
Next MY_WBOOKS
Application.ScreenUpdating = True
End Sub

have assumed sheet 1 is first in the tab order.
 

vac

Board Regular
Joined
May 21, 2002
Messages
211
Thanks for the reply. Maybee it was with a macro but I cant find it for the life of me, lol. thanks for the script but im getting an arror on the MY_WBOOKS line?
Any ideas??
Thanks again
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Which line exactly, the top or bottom 1.

Also what is the tab order of your sheets?
 

vac

Board Regular
Joined
May 21, 2002
Messages
211
Order is Sheet1, Sheet2 Sheet3 etc
Sheet1 is where i want the info to goto.

Error is runtime error 9
Subscript out of range
debug points to:
Sheets(MY_WBOOKS).Activate
Thanks
 

Forum statistics

Threads
1,136,878
Messages
5,678,300
Members
419,753
Latest member
Sallylwy

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
Top