Metusion
01-16-2012, 03:22 AM
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:
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
Notice this part:
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("MGB")
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.
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:
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
Notice this part:
'Change the sheet name of the database workbook
Set DestSh = DestWB.Worksheets("MGB")
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.