Pulling Data from multiple workbooks and compiling using VBA

TexasOil91

New Member
Joined
Jun 3, 2015
Messages
2
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:
<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>
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top