Pulling data from sheet to mastersheet

[TooN]

Hello,

I hope somebody can help me with this problem, it will save me a lot of time.
I have a sheet that has about 3000 lines. Here is a small example of the
sheet:
(It has about 10 columns more)

Column A Column B Column C Column D

0001 0005 ED05
0002 0005 A940 ED05
0003 0006 ED05
0004 0006 E941 ED05
0005 0007 ED05
0006 0007 E943 ED05
0007 0008 ED07
0008 0008 K235 ED07
0009 0008 K240 ED07
0010 0008 K245 ED07
0011 0009 ED07
0012 0009 E495 ED07
0013 0011 ED07
0014 0011 E289 ED07
0015 0012 ED09
0016 0012 EK01 ED09
0017 0020 ED07
0018 0025 ED03
0019 0025 K430 ED03
0020 002B ED07
0021 002B E945 ED07

The 4th column are departments (ED05, ED09 etc). I have filtered the
mastersheet by department and copied the data to different sheets. Ive been
entering data in column 5 till 15 the last few weeks. The data that ive
entered is on the sheets that i filtered, not on the mastersheet (sheet1). I
would like to have a formula or a macro (even better) that copies the data to
the mastersheet. A reference would be column A because its a unique number (1
to 2834). Can you pleas help me out with this problem... thanks!

 

[DaveyJones]

I think I understand what you are saying but whatever you do, make a copy of
the spreadsheet before you try this. It's good advice whatever you do.
Name your master sheet Master and the other sheets as the department you
have on that sheet So if you have ED07 on sheet(2) then rename the sheet
ED07. The I think this should work, I haven't managed to test it because I
don't have the worksheet...

Sub Test()
Dim Dept As String, id As Integer, k As Long
For i = 1 To 3000 '3000 rows of data
Sheets("Master").Select
id = Cells(i, 1) 'sets id as cell column A row i which is id number
Dept = Cells(i, 4) 'sets Dept as Cell Column D Row i which is deptartment
Sheets(Dept).Select
Columns(1).Select 'Select id column
Cells.find(What:=id, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
k = Range(ActiveCell).Row
Rows(k).Select
Selection.Copy
Sheets("Master").Select 'Master being the name of the master sheet number
Cells(i, 1).Select
ActiveSheet.Paste
Next

End Sub

Give it a go and let me know what happens...

 

[TooN]

Hello DaveyJones,

thanks for the quick response... The actual data starts at row 2 so i
changed 1 To 3000 to 2 To 3000. After changing that i think its almost good,
the error im getting now is:

Run-time error '1004'
Method 'Range' of object '_Global' failed.

Its in this line of the macro:

k = Range(ActiveCell).Row

Thanks in advance for your help!

TooN

 

[DaveyJones]

Sub Test()
Dim Dept As String, id As Integer, k As Long, addrs As String
For i = 1 To 3000 '3000 rows of data
Sheets("Master").Select
id = Cells(i, 1) 'sets id as cell column A row i which is id number
Dept = Cells(i, 4) 'sets Dept as Cell Column D Row i which is deptartment
Sheets(Dept).Select
Columns(1).Select 'Select id column
Cells.find(What:=id, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
addrs = ActiveCell.Address
k = Range(addrs).Row
Rows(k).Select
Selection.Copy
Sheets("Master").Select 'Master being the name of the master sheet number
Cells(i, 1).Select
ActiveSheet.Paste
Next


End Sub


Try that. Sorry, I'm quite new to all this.

 

[TooN]

Its works almost perfect! All the data in the sheets is copied to the
mastersheet, its perfect... couldnt be better. There is only one strange
thing! Row 6 (unique number 5) does not copy correctly. In that line it
copies line 1 instead of 5?? very strange...

 

[DaveyJones]

Without seeing it, I can't be sure. Since it's only I=6 it should be quite
easy to step through the command. Go to Visual Basic and press F8 with the
code. This will step through the code one line at a time. See what happens.
Paraphrased , the code

Saves unique ID into id
Saves Department into Dept
Goes to the sheet with name stored in Dept
Searches for unique ID stored in id
Gets row number information is in
Copies that row
Goes to Master
Selects Cell Row 'i' column A
Pastes data
Next 'i'

Since this is set for whichever iteration it is, an error on one line could
mean an error on all lines so just check the rest. I did forget to change the
iterations from 1 To 3000 to 2 To 3000 if thats a problem.

The only other thing I can think of is that there is an error in the unique
id on 'Master' or 'ED05'. Just check when the code runs that the cell
selected to store in the variables id and Dept are the correct ones.

If that fails, I'm afraid you'll have to ask someone who knows what they are
talking about...

 

[TooN]

Thanks Dave.... it works perfect now!!

Without seeing it, I can't be sure. Since it's only I=6 it should be quite
easy to step through the command. Go to Visual Basic and press F8 with the
code. This will step through the code one line at a time. See what happens.
Paraphrased , the code

Saves unique ID into id
Saves Department into Dept
Goes to the sheet with name stored in Dept
Searches for unique ID stored in id
Gets row number information is in
Copies that row
Goes to Master
Selects Cell Row 'i' column A
Pastes data
Next 'i'

Since this is set for whichever iteration it is, an error on one line could
mean an error on all lines so just check the rest. I did forget to change the
iterations from 1 To 3000 to 2 To 3000 if thats a problem.

The only other thing I can think of is that there is an error in the unique
id on 'Master' or 'ED05'. Just check when the code runs that the cell
selected to store in the variables id and Dept are the correct ones.

If that fails, I'm afraid you'll have to ask someone who knows what they are
talking about...