Macro in excel

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.Open sname, 0





End Sub

Please advise.




  • 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. |
  • Thanks.It worked.
    I would also like to close the file from which I copied the data.Any ideas?
    Thanks in advance.
  • 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.

Howdy, Stranger!

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