| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Drawing across Data from different workbook using long list.
Hi folks, This one's a real doosie!
 :-(Although some of me previous questions have been basic learning, this really has me stumped. I can do it, but my way will take a long...LONG...time! So.....basically I have two workbooks. Book1 is my user interface, and Book2 is just a data storage workbook with a single worksheet. In Book2 I have a column of names for which I have created a dynamic named range for Column A. I have specified for this range that it will allow up to 3000 rows of data (Rows 2 to 3001 in Column A). The columns B to AH have other data corresponding to each name in Cloumn A....ok...so... In Book1 I have created a list box which is linked to the dynamic named range in Book2. Thus, my list box will always display only whatever names are included in those 3000 rows of column A (Maybe 20names out of a max of 3000 initially), and none of the blank rows further down........are you still with me?....The list box in Book1 has the standard cell link to display the row number of the name that has been selected when the user clicks on one in the list, (2,3,4,5.....2999,3000,3001). Also in Book 1, beside and around the list box I have a variety of cells, each of which displays the data corresponding to the employee, taken from the other columns in Book2. (In Book2 Cell A2 will be a name, Cell B2 telephone number, Cell C2 age etc to AH). There is a Botton beside the list box which says "Display Employee Data". When the user selects a name from the list and clicks the button all the other data for that name from Book2 will be drawn over using the Macro. At present I think I have to write this buttons Macro to use SELECT CASE. But I'll have 3000 CASE value lines of code to manually write. Surely there has to be a better way than typing in 3000 lines of VBA code for this? .....can I use a few different SUB/END SUB sections, and denote the cell link number as X or something in the code to draw the data across, and in my button macro only denote what X is equal to, VBA automatically adjusting to the value of X ? Now for the doosie......I actually have 72 data storage workbooks like Book2, each with dymanic ranges from 1000 to 5000 row values, so I'll be writing until next year doing it with Select Case!! HELP.....PLEasseeeeee!!!!! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drawing across Data from different workbook using long list.
I think you might be drowning yourself in detail. You could use a For...Next
loop with an If...Then statement to query book 2 based on a book 1 col A value, one at a time. If the value of col a item is found in book 2, some col, then copy the related data to somewhere in book 1. You only need one a few lines of code to do the whole workbook. "dim" wrote: Hi folks, This one's a real doosie! :-(Although some of me previous questions have been basic learning, this really has me stumped. I can do it, but my way will take a long...LONG...time! So.....basically I have two workbooks. Book1 is my user interface, and Book2 is just a data storage workbook with a single worksheet. In Book2 I have a column of names for which I have created a dynamic named range for Column A. I have specified for this range that it will allow up to 3000 rows of data (Rows 2 to 3001 in Column A). The columns B to AH have other data corresponding to each name in Cloumn A....ok...so... In Book1 I have created a list box which is linked to the dynamic named range in Book2. Thus, my list box will always display only whatever names are included in those 3000 rows of column A (Maybe 20names out of a max of 3000 initially), and none of the blank rows further down........are you still with me?....The list box in Book1 has the standard cell link to display the row number of the name that has been selected when the user clicks on one in the list, (2,3,4,5.....2999,3000,3001). Also in Book 1, beside and around the list box I have a variety of cells, each of which displays the data corresponding to the employee, taken from the other columns in Book2. (In Book2 Cell A2 will be a name, Cell B2 telephone number, Cell C2 age etc to AH). There is a Botton beside the list box which says "Display Employee Data". When the user selects a name from the list and clicks the button all the other data for that name from Book2 will be drawn over using the Macro. At present I think I have to write this buttons Macro to use SELECT CASE. But I'll have 3000 CASE value lines of code to manually write. Surely there has to be a better way than typing in 3000 lines of VBA code for this? ....can I use a few different SUB/END SUB sections, and denote the cell link number as X or something in the code to draw the data across, and in my button macro only denote what X is equal to, VBA automatically adjusting to the value of X ? Now for the doosie......I actually have 72 data storage workbooks like Book2, each with dymanic ranges from 1000 to 5000 row values, so I'll be writing until next year doing it with Select Case!! HELP.....PLEasseeeeee!!!!! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drawing across Data from different workbook using long list.
Thanks JLG,
That seems like a good idea....but... I made a new worksheet in my Book1 so that I no longer need to copy across from Book2 directly, because my list wasn't displaying when Book2 was closed. This new sheet is linked to Book2 and updates its data upon Book1 loading. So now my list is referencing a dynamic range on a worksheet called "3" in Book1.... .....anyway, there are 33 different items of data on each row, each in its own column. This is both in Book2 and worksheet "3", and each column item has a cell in Book1 sheet "Current Employees" that I want to copy to. I did out the code by hand for the first two possible cases in for this list which might give you a clearer idea of the complications, see bottom. Basically, using your suggestion, after the THEN statement, there are numerous cell referances which will change depending upon the list item from 1 to 3000 selected.Can you suggest the format of the code...something like: Sub EmployeeData() ' My cell link for the list is on the calculations sheet cell A1. Sheets("Calculations").Select For Range("A1").Value 'The sheet called "3" is displaying the same data as Book2 in the same layout. I want to copy my data from it. Sheets("3").Select 'I will enter a list of numbers from 1 to 3000 in column AI of sheet "3" to be equal to or different from the cell link value. If (Range("AI2:AI3001).Value) = Range Then 'Now I get stuck because for one instance I'd simply select each cell I need from sheet "3" to copy across to its corresponding cell on sheet 'Current Employees', but I cant do that here, because depending upon the value of the cell link I might want to copy the cells B2,C2,D2 etc to D9, D14,F17 etc, or I might want to copy B1874,C1874,D1874 etc to D9, D14, F17 etc.....am I explaining this ok? Here's the way I did it using select case, but it took ages just to do these two because after copy and pasting the code into the second case, I had to go back over it by hand and change the values for ("B2") on the 4th line below for Case = 1, to ("B3") for Case = 2, and I had to change this for C2 to C3, D2 to D3....AH2 to AH3. If using this method, I'll have to change every one of these values for columns B to AH, for 3000 cases all the way down to B3001,C3001,D3001,E3001,F3001 etc Select Case (Range("A17").Value) Case Is = 1 Sheets("3").Select Range("B2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D9").Select ActiveSheet.Paste Sheets("3").Select Range("C2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D11:F11").Select ActiveSheet.Paste Sheets("3").Select Range("D2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D12:F12").Select ActiveSheet.Paste Sheets("3").Select Range("E2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D14:F14").Select ActiveSheet.Paste Sheets("3").Select Range("F2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D15:F15").Select ActiveSheet.Paste Sheets("3").Select Range("G2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D16:F16").Select ActiveSheet.Paste Sheets("3").Select Range("H2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D17:F17").Select ActiveSheet.Paste Sheets("3").Select Range("I2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D18:F18").Select ActiveSheet.Paste Sheets("3").Select Range("J2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D20:F20").Select ActiveSheet.Paste Sheets("3").Select Range("K2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D22:F22").Select ActiveSheet.Paste Sheets("3").Select Range("L2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D24:F24").Select ActiveSheet.Paste Sheets("3").Select Range("M2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D26:F26").Select ActiveSheet.Paste Sheets("3").Select Range("N2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D28:F28").Select ActiveSheet.Paste Sheets("3").Select Range("O2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D30:F30").Select ActiveSheet.Paste Sheets("3").Select Range("P2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D32:F32").Select ActiveSheet.Paste Sheets("3").Select Range("Q2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D34:F34").Select ActiveSheet.Paste Sheets("3").Select Range("R2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J6:L6").Select ActiveSheet.Paste Sheets("3").Select Range("S2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J8:L8").Select ActiveSheet.Paste Sheets("3").Select Range("T2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J10:L10").Select ActiveSheet.Paste Sheets("3").Select Range("U2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J12:L12").Select ActiveSheet.Paste Sheets("3").Select Range("V2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J14:L14").Select ActiveSheet.Paste Sheets("3").Select Range("W2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J15:L15").Select ActiveSheet.Paste Sheets("3").Select Range("X2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J16:L16").Select ActiveSheet.Paste Sheets("3").Select Range("Y2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J17:L17").Select ActiveSheet.Paste Sheets("3").Select Range("Z2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J18:L18").Select ActiveSheet.Paste Sheets("3").Select Range("AA2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J20:K20").Select ActiveSheet.Paste Sheets("3").Select Range("AB2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J22:K22").Select ActiveSheet.Paste Sheets("3").Select Range("AC2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J24:K24").Select ActiveSheet.Paste Sheets("3").Select Range("AD2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J26:K26").Select ActiveSheet.Paste Sheets("3").Select Range("AE2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J28:K28").Select ActiveSheet.Paste Sheets("3").Select Range("AF2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J30:K30").Select ActiveSheet.Paste Sheets("3").Select Range("AG2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J32:K32").Select ActiveSheet.Paste Sheets("3").Select Range("AH2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J34:K34").Select ActiveSheet.Paste Sheets("3").Select Range("A2").Select Sheets("Current Employees").Select Range("A1").Select Case Is = 2 Sheets("3").Select Range("B3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D9").Select ActiveSheet.Paste Sheets("3").Select Range("C3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D11:F11").Select ActiveSheet.Paste Sheets("3").Select Range("D3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D12:F12").Select ActiveSheet.Paste Sheets("3").Select Range("E3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D14:F14").Select ActiveSheet.Paste Sheets("3").Select Range("F3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D15:F15").Select ActiveSheet.Paste Sheets("3").Select Range("G3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D16:F16").Select ActiveSheet.Paste Sheets("3").Select Range("H3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D17:F17").Select ActiveSheet.Paste Sheets("3").Select Range("I3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D18:F18").Select ActiveSheet.Paste Sheets("3").Select Range("J3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D20:F20").Select ActiveSheet.Paste Sheets("3").Select Range("K3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D22:F22").Select ActiveSheet.Paste Sheets("3").Select Range("L3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D24:F24").Select ActiveSheet.Paste Sheets("3").Select Range("M3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D26:F26").Select ActiveSheet.Paste Sheets("3").Select Range("N3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D28:F28").Select ActiveSheet.Paste Sheets("3").Select Range("O3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D30:F30").Select ActiveSheet.Paste Sheets("3").Select Range("P3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D32:F32").Select ActiveSheet.Paste Sheets("3").Select Range("Q3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D34:F34").Select ActiveSheet.Paste Sheets("3").Select Range("R3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J6:L6").Select ActiveSheet.Paste Sheets("3").Select Range("S3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J8:L8").Select ActiveSheet.Paste Sheets("3").Select Range("T3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J10:L10").Select ActiveSheet.Paste Sheets("3").Select Range("U3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J12:L12").Select ActiveSheet.Paste Sheets("3").Select Range("V3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J14:L14").Select ActiveSheet.Paste Sheets("3").Select Range("W3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J15:L15").Select ActiveSheet.Paste Sheets("3").Select Range("X3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J16:L16").Select ActiveSheet.Paste Sheets("3").Select Range("Y3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J17:L17").Select ActiveSheet.Paste Sheets("3").Select Range("Z3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J18:L18").Select ActiveSheet.Paste Sheets("3").Select Range("AA3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J20:K20").Select ActiveSheet.Paste Sheets("3").Select Range("AB3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J22:K22").Select ActiveSheet.Paste Sheets("3").Select Range("AC3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J24:K24").Select ActiveSheet.Paste Sheets("3").Select Range("AD3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J26:K26").Select ActiveSheet.Paste Sheets("3").Select Range("AE3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J28:K28").Select ActiveSheet.Paste Sheets("3").Select Range("AF3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J30:K30").Select ActiveSheet.Paste Sheets("3").Select Range("AG3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J32:K32").Select ActiveSheet.Paste Sheets("3").Select Range("AH3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J34:K34").Select ActiveSheet.Paste Sheets("3").Select Range("A2").Select Sheets("Current Employees").Select Range("A1").Select End Select Sheets("Current Employees").Select End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drawing across Data from different workbook using long list.
Just a casual observation, but the problem might be more in the sheet data
layout than in the code structure. If you have to write a case statement that is that complex, then the data base must have been randomly constructed without consideration for future data retieval or manipulation. Of course, I don't know what your company requirements are either, so maybe I am blowing smoke. "dim" wrote: Thanks JLG, That seems like a good idea....but... I made a new worksheet in my Book1 so that I no longer need to copy across from Book2 directly, because my list wasn't displaying when Book2 was closed. This new sheet is linked to Book2 and updates its data upon Book1 loading. So now my list is referencing a dynamic range on a worksheet called "3" in Book1.... ....anyway, there are 33 different items of data on each row, each in its own column. This is both in Book2 and worksheet "3", and each column item has a cell in Book1 sheet "Current Employees" that I want to copy to. I did out the code by hand for the first two possible cases in for this list which might give you a clearer idea of the complications, see bottom. Basically, using your suggestion, after the THEN statement, there are numerous cell referances which will change depending upon the list item from 1 to 3000 selected.Can you suggest the format of the code...something like: Sub EmployeeData() ' My cell link for the list is on the calculations sheet cell A1. Sheets("Calculations").Select For Range("A1").Value 'The sheet called "3" is displaying the same data as Book2 in the same layout. I want to copy my data from it. Sheets("3").Select 'I will enter a list of numbers from 1 to 3000 in column AI of sheet "3" to be equal to or different from the cell link value. If (Range("AI2:AI3001).Value) = Range Then 'Now I get stuck because for one instance I'd simply select each cell I need from sheet "3" to copy across to its corresponding cell on sheet 'Current Employees', but I cant do that here, because depending upon the value of the cell link I might want to copy the cells B2,C2,D2 etc to D9, D14,F17 etc, or I might want to copy B1874,C1874,D1874 etc to D9, D14, F17 etc.....am I explaining this ok? Here's the way I did it using select case, but it took ages just to do these two because after copy and pasting the code into the second case, I had to go back over it by hand and change the values for ("B2") on the 4th line below for Case = 1, to ("B3") for Case = 2, and I had to change this for C2 to C3, D2 to D3....AH2 to AH3. If using this method, I'll have to change every one of these values for columns B to AH, for 3000 cases all the way down to B3001,C3001,D3001,E3001,F3001 etc Select Case (Range("A17").Value) Case Is = 1 Sheets("3").Select Range("B2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D9").Select ActiveSheet.Paste Sheets("3").Select Range("C2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D11:F11").Select ActiveSheet.Paste Sheets("3").Select Range("D2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D12:F12").Select ActiveSheet.Paste Sheets("3").Select Range("E2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D14:F14").Select ActiveSheet.Paste Sheets("3").Select Range("F2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D15:F15").Select ActiveSheet.Paste Sheets("3").Select Range("G2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D16:F16").Select ActiveSheet.Paste Sheets("3").Select Range("H2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D17:F17").Select ActiveSheet.Paste Sheets("3").Select Range("I2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D18:F18").Select ActiveSheet.Paste Sheets("3").Select Range("J2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D20:F20").Select ActiveSheet.Paste Sheets("3").Select Range("K2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D22:F22").Select ActiveSheet.Paste Sheets("3").Select Range("L2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D24:F24").Select ActiveSheet.Paste Sheets("3").Select Range("M2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D26:F26").Select ActiveSheet.Paste Sheets("3").Select Range("N2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D28:F28").Select ActiveSheet.Paste Sheets("3").Select Range("O2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D30:F30").Select ActiveSheet.Paste Sheets("3").Select Range("P2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D32:F32").Select ActiveSheet.Paste Sheets("3").Select Range("Q2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D34:F34").Select ActiveSheet.Paste Sheets("3").Select Range("R2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J6:L6").Select ActiveSheet.Paste Sheets("3").Select Range("S2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J8:L8").Select ActiveSheet.Paste Sheets("3").Select Range("T2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J10:L10").Select ActiveSheet.Paste Sheets("3").Select Range("U2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J12:L12").Select ActiveSheet.Paste Sheets("3").Select Range("V2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J14:L14").Select ActiveSheet.Paste Sheets("3").Select Range("W2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J15:L15").Select ActiveSheet.Paste Sheets("3").Select Range("X2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J16:L16").Select ActiveSheet.Paste Sheets("3").Select Range("Y2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J17:L17").Select ActiveSheet.Paste Sheets("3").Select Range("Z2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J18:L18").Select ActiveSheet.Paste Sheets("3").Select Range("AA2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J20:K20").Select ActiveSheet.Paste Sheets("3").Select Range("AB2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J22:K22").Select ActiveSheet.Paste Sheets("3").Select Range("AC2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J24:K24").Select ActiveSheet.Paste Sheets("3").Select Range("AD2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J26:K26").Select ActiveSheet.Paste Sheets("3").Select Range("AE2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J28:K28").Select ActiveSheet.Paste Sheets("3").Select Range("AF2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J30:K30").Select ActiveSheet.Paste Sheets("3").Select Range("AG2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J32:K32").Select ActiveSheet.Paste Sheets("3").Select Range("AH2").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("J34:K34").Select ActiveSheet.Paste Sheets("3").Select Range("A2").Select Sheets("Current Employees").Select Range("A1").Select Case Is = 2 Sheets("3").Select Range("B3").Select Application.CutCopyMode = False Selection.Copy Sheets("Current Employees").Select Range("D9").Select ActiveSheet.Paste Sheets("3").Select Range("C3").Select |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drawing across Data from different workbook using long list.
I dont think it is....
The program in summary..... I have various categories of data such as employees, various different materials, tax settings etc. Each category has its own data storage workbook. I also have a main user interface workbook. The user never manually uses the data workbooks only the interface workbook. The users data is always saved from the interface workbook to the seperate data workbooks, so that I can update the interface workbook at a later date, and the user can simply overwrite the 'old' interface workbook without the user losing and having to re-input all their data, which is what would happen if the data was in the same workbook. The data workbooks are organised with each 'section' of data on a single row. For example, in one workbook employee data is recorded, and each employees details can be considered a seperate section, and hence has its own row. This is so that I can allow the user to save new employees by inserting a blank row at the top, and copying data into it each time. Thus the list of employees and their data gets longer and longer each time the user enter a new employee. I will also be able to allow editing or removing of individual sections of data by deleting rows. Each time the user enters employee details such as name, age, address, phone etc in the user interface workbook, and clicks the save button, the info is copied and saved in the employee data workbook for later retrival. The user must be able to later view any of the data that they have stored. In this case the data stored for any individual employee. I could simply copy the relevant row back from the data workbook to the user interface as is, and yes, the user could see it, but it looks crappy. So where the data is called back to the user interface, I have laid it out differently on the page to look better, and thats what is causing so many different lines of code, because there have to be 33 seperate cells copied instead of a single row. Hence the age which is in column B on the data workbook is now moved down and onto column d on my user interface. Each cell in the data storage workbook is in a different location after bringing it back for presentation. There is a more user friendly look to the user interface....thats why its the user interface! lol Anyway, now I think you might get the idea. If I have to do a three thousand long 'Select Case' for this then so be it, but I know in my heart that there's a better and simpler way, but because of lack of experience, I don't yet know what it is!! All suggestions are welcome.... "JLGWhiz" wrote: Just a casual observation, but the problem might be more in the sheet data layout than in the code structure. If you have to write a case statement that is that complex, then the data base must have been randomly constructed without consideration for future data retieval or manipulation. Of course, I don't know what your company requirements are either, so maybe I am blowing smoke. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Drawing across Data from different workbook using long list.
I THINK I HAVE IT!! lol :-) But need a little bit of help!
I was reading my last reply and something occured to me! Instead of using the cell link number to decide which data to copy across directly into the various different cells, I will simply copy the row of data from the data workbook into a row on a different sheet in my user interface book. I can then copy these cells into the sheet with the nice layout. Hence I only need one set of code going to the sheet with the nice layout because the same row on the other sheet will have the required info in it! Daa Daaaa!! ;-) So....I need to know how to specify a row to copy across...something like this...would the two bit's with the 'n' work?...I made this up off the top of my head... Sub EmployeeData() Sheets("Calculations").Select n = (Range("A1").Value) Workbooks.Open Filename:= _ "C:\Program Files\Book2.xls" Rows(Cells(n, 1).Value).Copy Windows("Book1.xls").Activate Sheets("NewSheet").Select Rows("2:2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Windows("Book2.xls").Activate ActiveWorkbook.Close Windows("Book1.xls").Activate End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Validation and a Long List. An Easier Way? | Excel Discussion (Misc queries) | |||
| Extracting data from a long list | Excel Worksheet Functions | |||
| Extracting data from a long list | Excel Worksheet Functions | |||
| Retrieve multiple data rows data from a very long list and copy t | Excel Discussion (Misc queries) | |||
| Q. Consolidating a long list, in single workbook. | Excel Programming | |||
Linear Mode
