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

**satishdu** · 05-18-2014, 05:23 AM

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.xlsx Output_File.xlsx

Please help me on this.

I am new on vba so still learning.

**patel45** · 05-18-2014, 05:58 AM

why not attach a xlsm file with your macro ?

**satishdu** · 05-18-2014, 06:11 AM

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

**patel45** · 05-18-2014, 08:02 AM

you don't need macro for this job, put a formula for result and use filter for product

**satishdu** · 05-18-2014, 10:22 AM

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.

**satishdu** · 05-25-2014, 03:31 AM

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

**patel45** · 05-25-2014, 11:58 AM

Sub mvcheck()
Set wb = ActiveWorkbook
Dim fd As FileDialog
Dim filechosen As Boolean
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "Excel File", "*.xls*"
fd.FilterIndex = 1
fd.AllowMultiSelect = False
fd.Title = "Select MV File"
filechosen = fd.Show
If Not filechosen Then
  MsgBox " Kindly select your file "
  Exit Sub
End If
fd.Execute
Application.ScreenUpdating = False
Set wbi = ActiveWorkbook
With wbi.Worksheets("Sheet1")
  .Range("$A:$D").AutoFilter
  .Range("$A:$D").AutoFilter field:=3, Criteria1:="Loan"
  .Range("A1").CurrentRegion.Copy
End With
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 Sub

**satishdu** · 05-25-2014, 01:09 PM

Thank you patel45.

**patel45** · 05-25-2014, 01:44 PM

Sub mvcheck()
Dim objExcel As Excel.Application
Dim wbi As Excel.Workbook
Set wb = ActiveWorkbook
With Application.FileDialog(msoFileDialogOpen)
  .InitialFileName = ThisWorkbook.Path & "\*.xls*"
  .Title = "Please Select a File"
  .AllowMultiSelect = False
  .Show
  If .SelectedItems.Count = 0 Then
    MsgBox " Kindly select your file "
    Exit Sub            ' User clicked cancel: Exit
  End If
  strFile = .SelectedItems(1)
End With
Set objExcel = New Excel.Application
Set wbi = objExcel.Workbooks.Open(strFile)
With wbi.Worksheets("Sheet1")
  .Range("$A:$D").AutoFilter
  .Range("$A:$D").AutoFilter field:=3, Criteria1:="Loan"
  .Range("A1").CurrentRegion.Copy
End With

Workbooks.Add

Worksheets(1).Activate
Range("A1").PasteSpecial
wbi.Close True
objExcel.Quit
With Intersect(ActiveSheet.UsedRange.EntireRow, Range("E:E"))
  .FormulaR1C1 = "=RC[-1]*RC[-3]"
  .Value = .Value
  .Range("e1").Value = "Result"
End With
End Sub

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Macro to open workbooks, process data, save copy as a csv

Creating a Macro to save a copy before close

Creating Macro that will save a copy of the workbook to new directory + make it an add in

[SOLVED] Creating a macro to open as save other selected Excel sheets

[SOLVED] Creating an excel macro to open and save sheets to a different directory

Bookmarks

Bookmarks

Posting Permissions