import sheet

[unicorn11]

Hi to everybody,
I am new user in this forum and also I am new with excel macro , I need your help

I am creating a dashboard in excel composed by 1 sheet (called DB) with the data and other sheets with graphs and so on…
I would like to create a macro to import and update automatically the sheet DB with the data of another file.
The sheet DB in the dashboard is exactly the same of the sheet of the file that I need to import.

The script has to:
1) ask to the user where is the file to update
2) the user has to select the file
3) automatically it has to copy the values of a specific sheet in this file in the DB sheet in the dashboard

I found this script on line but I need help to adapt for what I need…

Sub Example5()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))

rnum = LastRow(basebook.Worksheets(1)) + 1

Set sourceRange = mybook.Worksheets(1).Range("A1:Z35000")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

basebook.Worksheets(1).Cells(rnum, "D").Value = mybook.Name
' This will add the workbook name in column D if you want

With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum, "A"). _
Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value

mybook.Close False

Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


Thanks in advance!!!

Luca

[patel45]

need sample files, source and target

[HaHoBe]

Hi, Luca,

please add code-tags to the procedure as requested in Forum Rule #3 - thank you.

Code is untested, and iou haven´t pointed out whether to append or replace the contents on sheet DB.

Please Login or Register  to view this content.

Ciao,
Holger