Copy into new workbook based on cell info and other questions

[Metusion]

I'm working on a project that seemed easy at first but is becoming more and more complex. I work at a bank as an intern and I'm in charge of creating a process that makes it easier to create the monthly statistic.
Whereas orders went to the currency trader on a physical piece of paper, we are now using excel to send the orders. It still sounds old fashioned, but they don't want to spend money on a new system such as Avaloq.

Now I set up an order sheet within excel and use a macro that transfers some of the important information into a new workbook that eventually adds up all this information to create a statistic. The issue is that all the information goes to different people. Now to my actual excel question (I gave some background info because I'll ask some other questions later).

I'm using the following Macro to copy information from one workbook to another. Now it says:

[vba]Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Change the file name (2*) and the path/file name to your file
If bIsBookOpen_RB("ERTRAEGE-VV.xls") Then
Set DestWB = Workbooks("ERTRAEGE-VV.xls")
Else
Set DestWB = Workbooks.Open("\\zhp11b01\group$\AZ\DEVISENERTRAEGE\ERTRAEGE-VV.xls")
End If
'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("OVERVIEW").Range("A2:G2")
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("MGB")
Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)
'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value
DestWB.Close savechanges:=True
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub[/vba]

Notice this part:

[vba]'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("MGB")[/vba]

This mean that the information is copied into the MGB sheet. Now I'd like the information of a cell to be dictating which sheet it will be copied into and not fixed information like MGB. How do I go about doing this?






My second question is how can I create a macro to add/delete information to a hidden sheet. This information is then used as a list which is normally named EVVLIST.

So the sequence would be --> Open box to enter name of new EVV or of the EVV to delete --> this then changes the information on the hidden sheet called (Source) --> the name of the list would have to stay EVVLIST to make sure the list still works on the other sheet.

[Bob Phillips]

maybe something like this

[vba]


Select Case ThisWorkbook.Sheets("OVERVIEW").Range("A2")

Case "A": Set DestSh = DestWB.Worksheets("MGB")

Case "B": Set DestSh = DestWB.Worksheets("Ford")

Case "B": Set DestSh = DestWB.Worksheets("Saab")

Case Else: Set DestSh = DestWB.Worksheets("Chrysler")
End Select
[/vba]

[Metusion]

I guess something along those lines, but good to know is that the cell information is the same as the name of the sheet.

I have about 24 sheets prepared such as, MGB, PBO, SVS etc.. The idea is that once people write for example MGB in cell A2 that the macro will use this information to send the relevant information to worksheet MGB.

So I'm trying to find out how you can change

Set DestSh = DestWB.Worksheets("MGB")

to Set DestSh = DestWB.Worsheet("use the name from cell A2")

I hope I'm clear enough

[Bob Phillips]

That should work

[vba]

Set DestSh = DestWB.Worsheet(Range("A1").Text)[/vba]

[Metusion]

That should work

[vba]

Set DestSh = DestWB.Worsheet(Range("A1").Text)[/vba]

that works! Thx a lot

any idea how I can tackle my second problem?

[Bob Phillips]

Not sure I understand that one.

[Metusion]

Not sure I understand that one.

yeah it's difficult to explain. Let me try again.

There's information listed on a hidden sheet for example:

A1
A2
A3
A4
A5

I've selected this list and given it a name which is EVVLIST
This information is used for a drop down menu on another sheet.

Now I'd like to create a macro that makes it easier to add information to the drop down without having to go to the hidden sheet (which is called Source), add the information and then hide it again.
I was thinking of using a textbox to enter the information, click on ok and then it should update the list on the hidden sheet hence the drop down. The reverse should also work to delete an entry from the list.

I hope it's more clear now. Thx

[Metusion]

yeah it's difficult to explain. Let me try again.

There's information listed on a hidden sheet for example:

A1
A2
A3
A4
A5

I've selected this list and given it a name which is EVVLIST
This information is used for a drop down menu on another sheet.

Now I'd like to create a macro that makes it easier to add information to the drop down without having to go to the hidden sheet (which is called Source), add the information and then hide it again.
I was thinking of using a textbox to enter the information, click on ok and then it should update the list on the hidden sheet hence the drop down. The reverse should also work to delete an entry from the list.

Also, after adding the information it should sort the list alphabetically.

I hope it's more clear now. Thx

sorry.. I wanted to edit my post, but quoted it by mistake...

[Bob Phillips]

You can write to hidden sheets without unhiding it

[vba]

Worksheets("Sheet4").Range("A1").End(xlDown).Offset(1, 0).Value = "Bob"
[/vba]

[Metusion]

thx for the reply

So how would I go about next?

The process should be to open up a textbox that gives the user the possibility to enter or delete an entry from the list on the hidden sheet.

For example:

[VBA]Input box to name new EVV (external asset manager) NewName = InputBox("Please Specify the name of the EVV", "New Copy")[/VBA]

and another macro to delete an EVV

[VBA]Input box to delete EVV (external asset manager) NewName = InputBox("Please Specify the name of the EVV", "New Copy")[/VBA]

after clicking ok the new information should be written or deleted from the hidden sheet. The dropdown menu that uses this information (EVVLIST) should automatically be updated.

I'm very new when it comes to macros and the ones I'm using so far I found by googling. I understand most of the code when I see it, but couldn't write it myself if I wanted to.