Drawing across Data from different workbook using long list.

D

dim

Hi folks, This one's a real doosie! :rolleyes: :-(

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!!!!!
 
J

JLGWhiz

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.
 
D

dim

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
 
J

JLGWhiz

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.
 
D

dim

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....
 
D

dim

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
 
D

dim

Hi folks,

For the benefit of anyone looking at this in the future I have this working
as:

Sheets("Calculations").Select
RangeValue = Range("A1").Value
Workbooks.Open Filename:= "C:\Program Files\Book2.xls"
Workbooks("Book2.xls").Activate
Rows(RangeValue).Copy
Workbooks("Book1.xls").Activate
Sheets("NewSheet").Select
Rows("2:2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Workbooks("Book2.xls").Activate
Activeworkbook.Save
ActiveWorkbook.Close
Workbooks("Book1.xls").Activate

Turned out to be straightforward enough! ;-)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top