Pulling Data from multiple workbooks and compiling using VBA

[TexasOil91]

Howdy all,

I am brand new here and am just looking for a little help with a problem I am having, any luck would be greatly appreciated! I am a beginner VBA user, and am trying to learn as much about it as I can.

The problem I am having is I have a large number of fields to look at and each field has a certain amount of wells. I made a "template" excel workbook that I use to run calculations and ascertain certain parameters of the wells for each field. What I am trying to do is run a macro which will allow me to change different parameters in a "master" workbook and then update these "template" workbooks with the new values, run the calculations, and then copy and paste the information (compile it) into a single sheet in the "master" spreadsheet.

The part I am struggling with is copy and pasting a variable range of cells into a document. I have attached my "master" spreadsheet and also a "template" file. I would appreciate any input, and am really thankful for yalls help!

This is what I have for my code so far:

View attachment 399090
View attachment 399091

<code>Option Explicit

Sub LoopThroughDirectory()

Dim MyFile As String
Dim erow
Dim Mpor, Npor, Msat, Nsat, B_o, Acres, a_, m_, n_, rw_ As Double
Dim n_wells As Long
Dim iter As Long
Dim RngtoCopy As Range, RngToPaste As Range

    Mpor = Worksheets("FINAL").Range("D3").Value
    Npor = Worksheets("FINAL").Range("E3").Value
    Msat = Worksheets("FINAL").Range("D4").Value
    Nsat = Worksheets("FINAL").Range("E4").Value
    B_o = Worksheets("FINAL").Range("H3").Value
    Acres = Worksheets("FINAL").Range("H4").Value
    a_ = Worksheets("FINAL").Range("K3").Value
    m_ = Worksheets("FINAL").Range("K4").Value
    n_ = Worksheets("FINAL").Range("M3").Value
    rw_ = Worksheets("FINAL").Range("M4").Value

MyFile = Dir("C:\Users\Shane\Desktop\Test\")

Do While Len(MyFile) > 0

If MyFile = "master.xlsm" Then
Exit Sub
End If

Workbooks.Open ("C:\Users\Shane\Desktop\Test\" & MyFile)
        
Worksheets("Final").Range("F2").Value = Mpor
Worksheets("Final").Range("G2").Value = Npor
Worksheets("Final").Range("F3").Value = Msat
Worksheets("Final").Range("G3").Value = Nsat
Worksheets("Final").Range("J2").Value = a_
Worksheets("Final").Range("J3").Value = m_
Worksheets("Final").Range("J4").Value = n_
Worksheets("Final").Range("L2").Value = rw_
Worksheets("Final").Range("P2").Value = B_o
Worksheets("Final").Range("P3").Value = Acres
        
Dim Total As Worksheet
Dim new_r As String

        
n_wells = Range("B3").Value
new_r = 7 + (n_wells - 1)
        
Range("A" & 7 & ":W" & new_r).Copy
        
ActiveWorkbook.Close savechanges:=True

erow = Worksheets("Total").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

ActiveSheet.Paste Destination:=Worksheets("Total").Range(Cells(erow, 1))


MyFile = Dir
        
Loop

End Sub</code>