My Excel VBA knowledge is unfortinately not where I want it to be, however, I am working to resolve this. I have been working a while now (2 days on and off) to create a macro.

Changing paths for source file & destination - I have integrated a "browse for file" into the macro (which works... wooohooo), as the source data and the "destination" report will both be in different locations every time the macro is used. So as you can guess, the idea of having a path hard coded into the VBA would be useless.

Need to "copy and paste" the source data (let's call this workbook "Source Mktg", and the worksheet tab "data") which is raw data that begins in cell A1 (headers in row 1), and the number of rows for this source data can vary from month to month, so cannot really use a traditional range. the entire worksheet would be pasted into an existing worksheet (the worksheet tab name will always be called "test", however the name of the workbook will vary, for now let's call the workbook "NY Mktg". However the macro will be run from the "NY Mktg" workbook, so it will always be open when the macro is run. I also want the source workbook to close after the data has been copied to the destination. Oh, and the source data is always a "csv" file.

Source worksheet name: Source Mktg
Source tab name: data

Destination worksheet name: NY Mktg
Destination tab name: test

Now I have some VBA written, but I am currently getting an error stating:

Run-time error '9':
Subscript out of range

This is the code I have so far. Please "forgive" the extra "comments" within my code, however this allows me to piece togather other bits of code, so i can determine which section does what (and get excited when one section actually works). It has been very helpful as I learn VBA.

Sub TestIt4()
'
' Browse for workbook
'
NewFN = Application.GetOpenFilename(FileFilter:="Excel Files (*.csv), *.csv", Title:="Please select a file")
If NewFN = False Then
MsgBox "You have not selected a file"
Exit Sub
Else
Workbooks.Open Filename:=NewFN
End If
'
' Copy active worksheet
'
ActiveSheet.copy
'
' macro copy from one workbook to another
'
    Dim fn, sh As Worksheet
    Set sh = Sheets("test")
    fn = Application.GetOpenFilename
    If fn = False Then
        MsgBox "Nothing Chosen", vbCritical, "Select workbook"
    Else
        Workbooks.Open fn
        ActiveSheet.UsedRange.copy sh.[A1]
        ActiveWorkbook.Close False
        ThisWorkbook.Activate
    End If
'
' Close all csv workbooks
'
Dim wkbk As Workbook

For Each wkbk In Application.Workbooks
If LCase(Right(wkbk.Name, 4)) = LCase(".csv") Then
wkbk.Close savechanges:=False
End If
Next wkbk

End Sub
Thanks in advance for everyone's time and help!