Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Macro in excel

ikrikumarikrikumar Member Posts: 6
Hi All,

I am working on a macro in excel to copy data from one file to the other.I am able to copy data from the other file but it prompts for the filename to where the data has to be copied..i click cancel twice and then it pastes the data to the other file.

Can I not copy the data without the prompting for the filename?

The code is as follows:-

Private Sub CommandButton1_Click()

Dim yr, mon, reportname As String

curfname = ActiveWorkbook.Name

Workbooks(curfname).Activate

Workbooks.Open sname, 0

Worksheets("Snapshot_Property").Range("B17:AB27").Select

Worksheets("Snapshot_Property").Range("B17:AB27").Copy

Workbooks(curfname).Activate

Worksheets("Snapshot_Property").Range("B17").Select

Worksheets("Snapshot_Property").Paste
End Sub

Please advise.

Thanks.

Regards,
Krishna

Comments

  • DaiMitnickDaiMitnick Member Posts: 77
    The line:

    Application.DisplayAlerts = False

    Should solve your problem, let us know if it doesn't. Also as you are selecting cells and opening workbooks I would use the line:

    Application.ScreenUpdating = False

    At the beginning to stop the screen flickering around, but that's up to you. Don't forget to set them both back to true at the end of the macro. Dai
    ------------------------------------------
    Do or do not, there is no try. |
    ------------------------------------------
  • ikrikumarikrikumar Member Posts: 6
    Thanks.It worked.
    I would also like to close the file from which I copied the data.Any ideas?
    Thanks in advance.
  • DaiMitnickDaiMitnick Member Posts: 77
    That's great. Yep it's just a simple:

    [red] Workbooks(sname).Close [/red]

    Where sname is the name (or you can use index instead) of the workbook to close. If you open up the bracket after the word close you will see a list of further options. Dai.
    ------------------------------------------
    Do or do not, there is no try. |
    ------------------------------------------
Sign In or Register to comment.