PDA

View Full Version : copy



oleg_v
12-20-2010, 01:29 PM
hi
i need some help with a macro:
i have an excel file i need to get file open window than to choose any excel file than to copy what in that excel file in sheet 1 to corrent sheet to cell A1 priviosly delete all data from the corrent sheet

thanks
oleg

mohanvijay
12-21-2010, 05:18 AM
try this


Dim fll As FileDialog
Dim filname As String

Set fll = Application.FileDialog(msoFileDialogFilePicker)
fll.Filters.Add "Excel files", "*.xls;*.xlsx;*.xlsm"

If fll.Show = -1 Then
filname = fll.SelectedItems(1)
End If

arr = Split(filname, "\")
wname = arr(UBound(arr))

Application.Visible = False

Workbooks.Open filname

ThisWorkbook.ActiveSheet.UsedRange.Clear

Workbooks(wname).Sheets(1).UsedRange.Copy ThisWorkbook.ActiveSheet.Range("a1")
Workbooks(wname).Close False

Application.Visible = True

oleg_v
12-22-2010, 01:49 AM
Hi
it wors great but the problem when i press a "cencel"
botton it gives me an error

what sould i do?



thanks

mohanvijay
12-22-2010, 03:20 AM
just put the code into if condition



Dim fll As FileDialog
Dim filname As String

Set fll = Application.FileDialog(msoFileDialogFilePicker)
fll.Filters.Add "Excel files", "*.xls;*.xlsx;*.xlsm"

If fll.Show = -1 Then

filname = fll.SelectedItems(1)
arr = Split(filname, "\")

wname = arr(UBound(arr))

Application.Visible = False

Workbooks.Open filname

ThisWorkbook.Sheets(1).UsedRange.Clear

Workbooks(wname).Sheets(1).UsedRange.Copy ThisWorkbook.ActiveSheet.Range("a1")
Workbooks(wname).Close False

End If

Application.Visible = True