How to open,copy and save as .. lookiing help on a samll macro for creating report

Hi Guys,

I am working on a macro and looking for little help on completing that macro. I am looking for help on below task.

I want to have three files under my macro

File 1 - First Spreadsheet would be carrying my macro under a command button would be give to perform the task.On Clicking the command button the input file would be open ,perform the diffrent function and save the new file using saveas option for giving location to save new file.


Capture_macro.JPG
file 2 - File 2 - Input FIle

Capture1.JPG

FIle 3 - OutPut file
Capture_Output.JPG
Through macro file, i would firstly browse the input file and after that it would create a new workbook with data of TV from input file by filtering on raw file and with extra column of result which going to reflect the multiplication of qty and price. It would also highlight the row whose result comes above 100. And at last that it ask us to save the output file.


I have attached my images, input file and output file for reference.

Input_FIle.xlsxOutput_File.xlsx

Please help me on this.

I am new on vba so still learning.

Hello Sir,

I am new on VBA so i know about getting the data from open worksheet, but now i know want to learn about getting data from closed workbook , create new file and then save new file at new location. And all i want to do these things ,with open and save as dialog box . Because of same i have no code file for this stuff.

Hope you can help me to learn this stuff by provide me code for doing this job.


Thanks
Satish

Hi ,

I want to automate my some daily jobs with VBA, and most of jobs include above stuff. If you could help here on this that would be highly appreciable. By learning i would be able to automate my jobs. So please help me to learn this stuff.

Hi All,

For above task , i have written below code, could someone plese help me to complete as i am getting error "out of range " on code line before applying filter.





Option Explicit

Sub mvcheck()

Dim fd As FileDialog
Dim filechosen As Boolean


Set fd = Application.FileDialog(msoFileDialogOpen)

fd.Filters.Clear
fd.Filters.Add "2003 File", "*.xls"
fd.Filters.Add "2007 & above File", "*.xlsx"
fd.FilterIndex = 1
fd.AllowMultiSelect = False

fd.Title = "Select MV File"
''fd.Button = "Run Rec"
filechosen = fd.Show

If Not filechosen Then
MsgBox " Kindly select your file "
Exit Sub
End If
fd.Execute

Application.ScreenUpdating = False


''Workbooks(filechosen).Sheet1.Activate

With Workbooks(filechosen).Worksheets("Sheet1")
.Range("$A:$D").AutoFilter
.Range("$A:$D").AutoFilter field:=3, Criteria1:="Loan"

Range("A1").CurrentRegion.Copy

Workbooks.Add

Worksheets(1).Activate
Range("A1").PasteSpecial

With Intersect(ActiveSheet.UsedRange.EntireRow, Range("E:E"))
.FormulaR1C1 = "=RC[-1]*RC[-3]"
.Value = .Value
Range("e1").Value = "Result"
End With

End With

End Sub