Free VBA code for Microsoft ACCESS

VBA to copy from one Excel sheet range to another
Workbook.

Here is a VBA example:
'--
Sub CopySource()
' VBA solution
' Example: Copy data from extern workbook in to this workbook
'--
''' Dim External (source) workbook object variables
Dim wbkSource As Workbook
Dim wksSource As Worksheet
Dim rngSource As Range
'--
Dim c As Range ' Represents a single cell
Dim strSheetName As String
' ' e.g. Paste Target Top-Left cell
Dim i As Integer
'--
''' Customize source workbook name
Const strWBK_SOURCE_NAME As String =
"SOURCE_BOOK.xls"
Const strWBK_SOURCE_PATH As String = "C:"
'--
''' Optional: List the names of worksheets
''' that n?eli? excluded from processing.
''' (customize worksheet names)
Const csEXCL_SHEET01 As String = "HOME"
Const csEXCL_SHEET02 As String = "AppData"
'--
''' Reference External (source) workbook object variable
On Error Resume Next ' #If Source wbk is not already open
Set wbkSource = Workbooks(strWBK_SOURCE_NAME)
'--
''' If Source wbk is not already open then open book
If wbkSource Is Nothing Then
Set wbkSource = Workbooks.Open( _
Filename:=strWBK_SOURCE_PATH & _
Application.PathSeparator & _
strWBK_SOURCE_NAME)
'--
''' If Source wbk could not be referenced then exit sub
If wbkSource Is Nothing Then
MsgBox "Workbook '" & strWBK_SOURCE_PATH & _
Application.PathSeparator & _
strWBK_SOURCE_NAME & "'" & vbLf & _
"on specified location couldn't be open." & vbLf & _
"Application is going to be terminated."
GoTo HandleExit '>>>
End If
End If
'--
''' Loop trough worksheet collection and
''' Copy/Paste source in This workbook.
For Each wksSource In wbkSource.Worksheets
''' Get worksheet name
strSheetName = wksSource.Name
''' Optional: Exclude specified worksheet
If strSheetName = csEXCL_SHEET01 Or _
strSheetName = csEXCL_SHEET02 Then
GoTo LabNextWorksheet '>>>
End If
'--
''' Reference source to be Copy/Paste
''' Customize the reference way as you need.
''' In this sample ...
''' example a) Reference complete Current region
Set rngSource = wksSource.Range("A1").CurrentRegion
''' example b) ... or reference only Column 'A' in Current region
Set rngSource = wksSource.Range("A1").CurrentRegion.Colu
mns(1)
'--
''' Copy/Paste referenced data
''' in to SAME NAME worksheet in This workbook,
''' in to Range 'A1'.
Err.Clear
rngSource.Copy _
ThisWorkbook.Worksheets(strSheetName).Range(" A1")
'--
''' Optional: Check if Copy/Paste successfull
If Err <> 0 Then
MsgBox "Data from worksheet '" & strSheetName & _
"' are not copied." & vbLf & _
"Maybe in your workbook, " & _
"the same name worksheet doesn't exist.", _
vbExclamation
End If
'--
LabNextWorksheet:
Next wksSource
'--
HandleExit:
''' Optional: Close soutce workbook
wbkSource.Close SaveChanges:=False
''' Free object variables
Set wbkSource = Nothing
Exit Sub
'--
HandleError:
MsgBox Err.Number & " - " & Err.Description, vbCritical
Resume HandleExit
End Sub
'--
Best Regards,
VBA Team

Free VBA Code