Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pulling data from sheet to mastersheet
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pulling data from sheet to mastersheet
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... -- Dave "TooN" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pulling data from sheet to mastersheet
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" wrote: 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... -- Dave "TooN" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pulling data from sheet to mastersheet
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. -- Dave "TooN" wrote: 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" wrote: 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... -- Dave "TooN" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pulling data from sheet to mastersheet
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" wrote: 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. -- Dave "TooN" wrote: 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" wrote: 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... -- Dave "TooN" wrote: 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pulling data from sheet to mastersheet
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... -- Dave "TooN" wrote: 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" wrote: 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. -- Dave "TooN" wrote: 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" wrote: 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... -- Dave "TooN" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pulling data from another sheet | Excel Discussion (Misc queries) | |||
Match and Sort for two range of data on different worksheets? | New Users to Excel | |||
Pull data from another sheet based on certain criteria | Excel Discussion (Misc queries) | |||
Combining data from cells from several excel sheets to a new sheet | Excel Discussion (Misc queries) | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions |