Re: Copy Excel Files from one folder to another
Thanks for the reply. I have amended your code as follows:
However when running the macro no files are copied to folder Summary Profit Figures.
Kindly test & amend
[COLOR=#0000ff]Sub[/COLOR] CopyADirectoryInclSubFolders()
Shell "cmd /c xcopy /y /t /e c:\pull\*.XL* C:\Summary Profit Reports"
[COLOR=#0000ff]End Sub[/COLOR]Re: Copy Excel Files from one folder to another
Hi Patel
Thanks for the help. I have two folders and several sub-folders that I needf to copy .xls files from
Kindly amend your code to incorpoarate this
Sub CopyPullfolders()
Dim SourceFolder As String, TargetFolder As String
SourceFolder = "C:\pull , C:\Z-ACCNTS"
TargetFolder = "C:\Summary Profit Reports\"
Call LoopController(SourceFolder, TargetFolder)
End Sub
Private Sub LoopController(sSourceFolder As String, TargetFolder As String)
Dim Fldr As Object, FL As Object, SubFldr As Object
Set Fldr = CreateObject("scripting.filesystemobject").Getfolder(sSourceFolder)
Set fso = CreateObject("Scripting.FileSystemObject")
For Each SubFldr In Fldr.SubFolders
fso.CopyFolder SubFldr, TargetFolder
Next
End SubI would like a macro to copy excel files from Folder "C:\downloads" including sub-directories with the download directory for eg C:\downloads\BR1 to folder "C:\Summary"
Your assistance in this regard is most appreciated
Re: Difficult Finance question
Hi Cytop
I cannot find the edit button. However, the problem is stated on the attached word documents and the questions are contained on the first sheet of the spreadsheet named "questions"
I have a problem pertaining to a loan repayment on a mortgage bond, an annuity as welll as a bond which I need to be resolved using Excel.
your assistance in this regard is most appreciated
Re: Regresion Analysis
Hi Wigi
Thanks for the reply I managed to sort the problem out. I took out an old Stats textbook and managed to calculate the Regression Analysis
Regards
Howard
I have sales data for 12 month and wish to forecast the sales for the next quarter. There has been a steady growth in sales of 8% per quarter. I would like to compute the regression analysis manually (Y = a +bX) showing how this is computed as well as using Excel's built in regression analysis function
Your assistance in this regard will be most appreciated
Re: VBA Code to Extract data
Hi Jindon
Thanks for the help
Re: VBA Code to Extract data
Hi Jindon
Thanks for the help. The only thing not being extracted in the body in the branch names as well as the amounts
Regards
Howard
Re: VBA Code to Extract data
Hi Jindon
I have another workbook similar to my previous problem
I would like to be able to double click on a name on Column C and would like the following data extracted into Outlook
The subject line must State Admin Comm
1) Comm
2) Branch Name for Eg BR1
3) Amount
I must be able to double click on a name and the above information must be extracted once for all unique names for eg if Peter Bythe appears more than once then all the information appearing in col B & D for Peter Blythe must be extracted for eg
Comm Br1 6502
Comm Br2 2996
Your assitance will be most appreciated
Regards
Howard
Re: VBA Code to Extract data
Thanks for all the assistance, macro works perfectly
Re: VBA Code to Extract data
Hi Jindon
Attached please find my file where I have copied your code into the sheet
When I double click on the amount it puts it into edit mode instead of extracting the data
Your assistance in resolving this issue will be most appreciated
Re: VBA Code to Extract data
forum.ozgrid.com/index.php?attachment/44426/
Hi Jindon
when I double click on the amount in the original file when I attached after changing the code temp = Cells(1, zCol) to temp = Cells(5, zCol), the amount is extracted correctly.
I right clicked on the sheet contained the code and copied the code onto another similar workbook. I then went to module11 & copied the code pertaining to emailing the data
However, when I double click on the amount in line with commission, I get compile error variable not defined and the following code is highlighted
zRow = Target.Row
It would be appreciated if you would check & advise on how I can correct this
Many thanks
Howard
Re: VBA Code to Extract data
Hi Jindon
Thanks for the help, much appreciated
Regards
Howard
Re: VBA Code to Extract data
Quote from jindon;599388Looks like 5, instead of 1, otherwise Runtime error when col.A clicked.
Codetemp = Cells(5, zCol)
Hi Jindon Thnks for the help. The run time error no longer occurs. I need the code to be amended so as to extract the text 'commission", as well as the value in line with the text commission for the applicable month i.e for the unhidden column
When I double click on commission in line with the email
I need the following to be extracted into Outloook Commission 10938 which will be emailed to
I will double click on each of the rows containing Commmision to extract the data to be emailed to the various managers
If you have an easier way to do this, your assistance wil be most appreciated
Each month I will select a month and the applicable column for the month will be unhidden. I only need the commission values for the applicable month to be extracted & emailed
I have a spreasheet that has email addresses in Col N and the months in row 1. I have a drop down that when I select a month , the column with the applicable month will opeen i.e be unhidden
when A double click on commission the amount in the same row as commision must be extracted as well as the branch code that is in Column A 5 rows above commission
I asked someome to assist me in with the code, but when I double click on Commission, I get run time error 13, type mismatch and the following code is highlighted
zMonth = CDate(temp)
See code & sample data below
Your assistance in resolvinng the problem will be most appreciated
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
zRow = Target.Row 'e.g. 21
temp = "a" & zRow 'e.g. "a21"
If Range(temp).Value <> "Commission" Then Exit Sub 'not on a Commission row
'**************************************************
'COMMISSION ROW DOUBLE-CLICKED, PROCEED..
'FETCH EMAIL ADDRESS..
temp = "n" & zRow 'e.g. "n21"
zMailTo = Range(temp).Value 'e.g. "[email protected]"
'test for missing email here, if required etc:
If Range(temp).Value = "" Then
'missing email addess, display message here..
Exit Sub
End If
'FETCH COMMISSION MONTH..
zCol = Target.Column 'e.g. 6 ;(= column [F] )
temp = Cells(1, zCol) '> date is in row 1 (or row 5??)
zMonth = CDate(temp)
'test for missing zMonth here, if required etc.
'FETCH COMMISSION AMOUNT..
zAmount = Target.Value 'cell that was double-clicked
'test form missing amount here, if required etc.
'FETCH BRANCH NAME..
temp = "a" & (zRow - 5) 'e.g. colA, 5 rows above clicked row
zBranch = Range(temp).Value 'e.g. "Br2"
'test form missing amount here, if required etc.
'assume all data is present and tested, so prepare email:
prepareEmail zMailTo, zMonth, zBranch, zAmount 'NOTE:in same order as email routine
Cancel = True 'ignore normal double-click-edit-cell
End Sub