Macro again..

[Sylvia]

Hi..

I have to write one macro which will add a button allowing Copy function. This button should allow us to copy the whole contents of some another Excel file (containing single tab) into the currently open file.

For this I also need to add a search box which will allow me to search and select the file in windows after clicking on the above button and then proceed further..

Can anyone help me for this please?

Thanks,
Sylvia

[Dave Peterson]

option explicit
sub testme()
dim myFileName as variant
dim newwkbk as workbook
dim CurWks as worksheet

myfilename = application.getopenfilename("excel files, *.xls")
if myfilename = false then
exit sub
end if

set curwks = activesheet

set newwkbk = workbooks.open(filename:=myfilename)

newwkbk.worksheets(1).copy _
before:=curwks

newwkbk.close savechanges:=false

end sub

May get you started.



Sylvia wrote:
>
> Hi..
>
> I have to write one macro which will add a button allowing Copy
> function. This button should allow us to copy the whole contents of
> some another Excel file (containing single tab) into the currently open
> file.
>
> For this I also need to add a search box which will allow me to search
> and select the file in windows after clicking on the above button and
> then proceed further..
>
> Can anyone help me for this?
>
> Thanks,
> Sylvia
>
> --
> Sylvia
> ------------------------------------------------------------------------
> Sylvia's Profile: http://www.excelforum.com/member.php...o&userid=30300
> View this thread: http://www.excelforum.com/showthread...hreadid=500573

--

Dave Peterson

[Sylvia]

Hi Dave..

Thanks for your help. But it is creating a new worksheet. I want to copy the content in the exesting worksheet only, after the existing content. I dont want to add new worksheet. How to do that?

I tried newwkbk.Worksheets(1).Copy Destination:=CurWks

But its not working.

Can you please help me for this?

Thanks,
Sylvia

[Dave Peterson]

option explicit
sub testme()
dim myFileName as variant
dim newwkbk as workbook
dim CurWks as worksheet
Dim destCell as range

myfilename = application.getopenfilename("excel files, *.xls")
if myfilename = false then
exit sub
end if

set curwks = activesheet
With curwks
set destCell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end with

set newwkbk = workbooks.open(filename:=myfilename)

newwkbk.worksheets(1).usedrange.cells.copy _
destination:=destcell

newwkbk.close savechanges:=false

end sub

This assumes that I can use column A to determine the next available row of the
current sheet.

Sylvia wrote:
>
> Hi Dave..
>
> Thanks for your help. But it is creating a new worksheet. I want to
> copy the content in the exesting worksheet only, after the existing
> content. I dont want to add new worksheet. How to do that?
>
> I tried newwkbk.Worksheets(1).Copy Destination:=CurWks
>
> But its not working.
>
> Can you please help me for this?
>
> Thanks,
> Sylvia
>
> --
> Sylvia
> ------------------------------------------------------------------------
> Sylvia's Profile: http://www.excelforum.com/member.php...o&userid=30300
> View this thread: http://www.excelforum.com/showthread...hreadid=500573

--

Dave Peterson