Macro Help

[sarfarazbutt]

I have two separate files in excel, FILE(A) contain all the data and FILE(B) is summary file, I would like create macro in file(B) and this macro open file(A) insert one row empty in line 1 and save and return back to File(A).

I would love if this macro ask the path of File(B) as may be need to change the folder.

 

[iliace]

Try this:

Public Sub GetFileB()
  Dim strFileB As String
  Dim wkbFileB As Excel.Workbook
  Dim wshFileB As Excel.Worksheet

  With Application.FileDialog(Office.MsoFileDialogType.msoFileDialogFilePicker)
    .AllowMultiSelect = False
    With .Filters
      .Clear
      .Add "Excel Files", "*.xl*"
    End With
    If .Show = False Then
      Exit Sub
    End If
    strFileB = .SelectedItems(1)
  End With

  On Error Resume Next
  Set wkbFileB = Application.Workbooks.Open(strFileB)
  On Error GoTo 0
  
  If wkbFileB Is Nothing Then
    Call MsgBox("Error opening file" & strFileB, vbOKOnly + vbExclamation)
    Exit Sub
  End If
  
  Set wshFileB = wkbFileB.Worksheets(1) 'change 1 to "SheetName"
  wshFileB.Rows(1).Insert Excel.xlShiftDown
  wkbFileB.Save
  wkbFileB.Close False
End Sub