Hi Guys,
For the below code, if let's say i wanted to only copy and paste data matched by column headings, how can that be done?
Could someone please help? Many many thanks.
For the below code, if let's say i wanted to only copy and paste data matched by column headings, how can that be done?
Could someone please help? Many many thanks.
Code:
Option Explicit
Sub ConsolidateDta()
Dim i As Integer
Dim fil As String
Dim Col As String
Dim cpy As String
Dim ws As Worksheet
Dim twb As Workbook
Set ws = Sheet1 ' List sheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set twb = ThisWorkbook
On Error GoTo Err
For i = 2 To ws.Range("B65536").End(xlUp).Row 'Sheet1 is MasterSheet
fil = ws.Range("C" & i) & ws.Range("B" & i) 'File Location plus XL name
cpy = ws.Range("D" & i) & ":" & ws.Range("E" & i) 'Copy Range
Col = Left(ws.Range("B" & i), 1) 'Col to paste to
Workbooks.Open fil, 0, 1 'Open Read Only
Range(cpy).Copy
twb.Sheets(ws.Range("F" & i).Value).Cells(Rows.Count, Col).End(xlUp)(2).PasteSpecial 12 'Vals only
ActiveWorkbook.Close False 'Close no save
Next i
Application.DisplayAlerts = False
Application.ScreenUpdating = True
Exit Sub
Err:
MsgBox "The file " & ws.Range("b" & i) & " is missing. Operation incomplete."
End Sub