VBA help: opening, copying, pasting a range of files

[donut]

Hello, I'm new here. I know this has been asked in some form in the past, but I'm having trouble tweaking a macro to do as I command.

What I want the macro to do for me is (in this order too I guess)
1)open a master file (got this, I think)

2) (this part will loop)
-open a file from a list of files in a spreadsheet
-copy a range of cells
-paste it into a master file
-close workbook
-open next file on list
etc.


Basically I have 3 problems:

1) I can get the darned thing to summon one sheet based on a cell value... I.e. I use some form of concatenation and alchemy to create the file path, but I cannot get it to read a list of extensions.

2) I have no idea where to throw in the copy and paste portions, or how to switch back and forth between the 2 open sheets (the workbook name will be changing with each new sheet)

3) When using something like auto filter, would this be able to skip over the hidden cells?

So I have these 2 macros, that I have managed to tweak to a certain point.

Sub MKTG()
'
' MKTG Macro
' Copies and pastes balance sheets to marketing spending
'

'
    Dim ThisPath As String
    ThisPath = ActiveWorkbook.Path
    Workbooks.Open Filename:=Range("C2").Value
       
    
End Sub

and...

Sub MKTG2()
'
' MKTG2 Macro
' copies and pastes balance sheet items into spending file
'

'
    Dim ExtFile As String
    Dim ExtBk As Workbook
     
    ExtFile = Range("C2").Value
    If Not ExtFile = "" And Dir(ExtFile) <> "" Then
    Else
        ExtFile = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xls), *.xls", Title:="Please Select Service A File")
    End If
    On Error Resume Next
    Set ExtBk = Workbooks(Dir(ExtFile))
    On Error GoTo 0
    If ExtBk Is Nothing Then
        Application.Workbooks.Open ExtFile
        Set ExtBk = Workbooks(Dir(ExtFile))
    End If
End Sub

TBH, the second one doesn't make much sense to me, hell most of this VBA sorcery type stuff scares me.

Alas, maybe I have too much black or yellow bile in my body, or maybe its because I haven't been bled in a few months, but I have been unable to reach my answer via google. It would help me out, this mindless copy/paste thing is borderline scaphism.

(sorry for the random bits of nerdery)

 

[donut]

Oh, maybe to help clarify.

I will have a list of:

person who handles entity (col A) (which I will use auto filter to sort by the name)
entity (col B)
file names (col C)

every sheet that will be opened will have a range named MKTG

every entity will have a tab on the master file, where everything will be pasted.


Oh and I'm using '03, at the moment.

 

[donut]

Bump. Man this fell like a stone.

 

[donut]

I guess there is no hope, then.

Vahalla awaits this thread.

Farewell my question, I barely knew thee.