VBA Code for C&P from two workbooks into a Master Workbook

[JTS25]

Hi all,

New to this board, and hoping to get some good advice.

My team conducts a lot of data pulls to create reports, and they are all very time consuming. I'm trying to better educate myself in becoming more efficient in excel. One of the reports we do, is a Reconciliation between two systems.

This consists or 3 separate workbooks ( CMF Database, CMS Database, Master Workbook).
Folder Location is: K:\Technologies\DOT\Reports\Tracking & Status Sheets\CMS_DB Reconcile

Within the Master Workbook A-H, K, O, R, U, X - are all copy and pasted data from CMF Database workbook. Columns I, L, P, S, V, Y - are all vlookups to pull in data from CMS Database Workbook. Columns J, M, Q, T, V, Z - are the deltas ( if Columns H & I are the same = True, if they are different = False w/ color code).

Could someone help me out with the C&P part of this code, and then I will worry about the Vlookups at another time.

All three of the workbooks are named the same, have default formats, and are saved in the same location each time.

Is there a VBA Code or Macro that could be created, so when workbook 1 and workbook 2 are created and saved in the location with the Master Recon Template. Could I open the Master Recon Template and execute the code to complete the Reconciliation instead of doing the manual manipulation?

Thank you in advance for any advice or information provided.

 

[JTS25]

I just realized I didn't provide enough details.

All three of the workbooks will live in the same location, will have the same names, and will also have the same format each time. Except the number of rows will vary each time the spreadsheets are created.

Below will be the Columns that are needed to be copied and pasted from the CMF Database workbook --> Master Workbook:
CMF Column to paste to Master Workbook column, and each column will begin on row 2 and will need to be pasted to row 2 (A2 --> D2, ect..)
A --> D
B --> E
C --> C
F --> J
I --> R
L --> S
P --> AA
S --> AC
V --> AI

Would the below VBA work:
Sub CopyRange()
Application.ScreenUpdating = False
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ThisWorkbook
Dim LastRow As Long
Const strPath As String = "K:\Technologies\DOT\Reports\Tracking & Status Sheets\CMS_DB Reconcile"
ChDir strPath
strExtension = Dir("*.xls*")
Do While strExtension <> ""
Set wkbSource = Workbooks.Open(strPath & strExtension)
With wkbSource
LastRow = .Sheets("CMS Database sheet").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
.Sheets("CMS Database sheet").Range("A2,B2,C2,F2,I2,L2,P2,S2,V2" & LastRow).Copy wkbDest.Sheets("Master Workbook").Cells(Rows.Count, "D2,E2,C2,J2,R2,S2,AA2,AC2,AI2").End(xlUp).Offset(1, 0)
.Close savechanges:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub

 

[JTS25]

Hi all,

I tried a different VBA code, and keep recieving a Run-Time Error 450 (Wrong number of arguments or invalid property assignment). Would anyone be able to assist me please.

ub ImportRawData()




Dim c As Long
Dim Col As Variant
Dim Filename As String
Dim Filepath As Variant
Dim rngBeg As Range
Dim rngEnd As Range
Dim rngDst As Range
Dim rngSrc As Range
Dim rowsize As Long
Dim wkbDst As Workbook
Dim wkbSrc As Workbook

Set wkbDst = ThisWorkbook
Set rngDst = wkbDst.Worksheets("Reconcile Master").Range("A2", "B2", "C2", "F2", "I2", "L2", "P2", "S2", "V2")

Filepath = "K:\Armament Technologies\DOTC\Reports\Tracking & Status Sheets\CMS_DB Reconcile"
Filename = "InitiativeViewExportActive.xlsx"

On Error Resume Next
Set wkbSrc = Workbooks(Filename)
If Err = 9 Then
If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
If Filename = "False" Then Exit Sub
Set wkbSrc = Workbooks.Open(Filename)
End If
On Error GoTo 0

' Clear previous data.
rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count).ClearContents

' Import the data.
With wkbSrc.Worksheets("CMF Data").UsedRange
' Step through the source data columns.
For Each Col In Array("D", "E", "C", "J", "R", "S", "AA", "AC", "AI")
' Data starts on row 2.
Set rngBeg = .Parent.Cells(2, Col)

' Find the row where the data ends in this column.
Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)

' Number of rows in this column.
rowsize = rngEnd.Row - rngBeg.Row

If rowsize > 0 Then
Set rngSrc = .Parent.Range(rngBeg, rngEnd)
rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
End If

' Increment the column offset.
c = c + 1
Next Col
End With

End Sub

 

[JTS25]

Any help would be appreciated, or a good starting point.

 

[JTS25]

Any help would be greatly appreciated. I'm trying to learn VBA from Linkedin Learning, but I keep hitting road blocks trying to complete this code from what I have learned and information from similar threads on this site.

We create a lot of reports, from exports out of Access, then we do a lot of C&P, VLookups, and conditional formatting into master template sheets for reporting purposes.

What is the best way to learn how to write VBA for copying columns from workbook1 into workbook2.