Data import/extraction question

[tempacc]

Hey All,

I receive a weekly statisical report (in excel) from which data must b
added to an existing monthly report (in excel). After researching an
writing a simple macro, I realized I had no idea what I was doing an
needed dire help. This is what I need the macro to do.

1) Prompt the user to choose which file from which to extract data.
(perhaps a "Application.GetOpenFilename"?)

2) Extract certain cell specific data from a specific worksheet o
spreadsheet #2, and add it to existing cell/worksheet specific data i
spreadsheet #1.

3) Be able to reformat and reuse this macro for other uses. (Once I se
how the macro works, I should be able to figure this out)

Any help would be greatly appreciated. Thank

 

[Guest]

This may help some. Without details about what is to be copied where, it is
difficult to address that part of your question.

this opens a workbook, then copies the data from cell "A1" of the
activesheet (of the source workbook) to my destination sheet (Sheet1 - I'm
using the codename for the worksheet). the destination range is column A of
the next available row of the worksheet.


Sub ExtractData()
Dim SourceWkBk As Workbook
Dim DestWkSht As Worksheet
Dim DestRow As Long

On Error Resume Next
Set DestWkSht = ThisWorkbook.Worksheets(Sheet1.Index)

If IsEmpty(DestWkSht.UsedRange) Then
DestRow = 1
Else: DestRow = DestWkSht.UsedRange.Rows.Count + 1
End If

If Application.Dialogs(xlDialogOpen).Show Then
Set SourceWkBk = ActiveWorkbook
Else: Exit Sub
End If

SourceWkBk.ActiveSheet.Range("A1").Copy _
DestWkSht.Cells(DestRow, 1)

SourceWkBk.Close

End Sub