I'm trying to copy some data from one worksheet to another (conditionally).
In the master worksheet, I have a lot of data in rows and columns.
One of the columns has specific information (names) and I'm trying to copy only certain rows/columns based on whats in the "names" column.
A B C D
1 John Blue Play Bulb
2 Jack Black Run Toy
3 Dave Brown Home Desk
4 Jack Grey Work Door
5 Brian Green Shed Chair
I want code that copies all rows that contain "Jack" in column A to a new worksheet. The worksheet should be named "Jack" and only certain columns should be copied, such as columns A, and C.
Therefore the new worksheet (Jack) will be:
1 Jack Run
2 Jack Work
Does anyone know how I can achieve this?
I have been using bits and pieces code from this website to try and get this to work but to no avail!
I found some logic but keep getting error: "Runtime error '9': Subscript out of range."
This is a start and I can build on it if I can get it working!
I'm using Excel 2010.
Private Sub cmdGetData_Click()
Dim lastListRow, myItems, destRow As Long
Dim shtName As String
'Determine how many rows from Sheet1 to copy
lastListRow = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Loop through rows
For myItems = 2 To lastListRow
'Set Sheet name based on value in Sheet1 Column A for each list item
shtName = Sheets(1).Range("A" & myItems)
'Determine next empty row in Sheet with the same name
'as the value in Sheet1 Column A
destRow = Sheets(shtName).Range("A" & Rows.Count).End(xlUp).Row
'Copy current list item to proper sheet
Destination:=Sheets(shtName).Range("A" & destRow)