chipsworld
Board Regular
- Joined
- May 23, 2019
- Messages
- 161
- Office Version
- 365
All,
Thanks to GlennUK, I have gotten this far, but am now perplexed...
Using "Find" to import the data to multiple columns and rows was a great idea, but now I need to import additional data to those same ranges and sum it with what is already there...
How? I have been trying to figure this out for a couple of days now with no luck... Any help would be awesome!
Here is what I have thus far...
As you can see, I allow the user to select the file which contains the data (SourceW), then import it to the master sheet (TargetW). However, I need to import additional data to a few of the ranges in TargetW (Ranges defined as Tar) and add the additional numbers to the data values already in those ranges.
Thanks to GlennUK, I have gotten this far, but am now perplexed...
Using "Find" to import the data to multiple columns and rows was a great idea, but now I need to import additional data to those same ranges and sum it with what is already there...
How? I have been trying to figure this out for a couple of days now with no luck... Any help would be awesome!
Here is what I have thus far...
As you can see, I allow the user to select the file which contains the data (SourceW), then import it to the master sheet (TargetW). However, I need to import additional data to a few of the ranges in TargetW (Ranges defined as Tar) and add the additional numbers to the data values already in those ranges.
VBA Code:
Private Sub cmdImport_Click()
Dim filter As String
Dim caption As String
Dim SourceF As String
Dim SourceW As Workbook
Dim TargetW As Workbook
Dim SourceS As Worksheet
Dim TargetS As Worksheet
On Error Resume Next
Set TargetW = Application.ThisWorkbook
' get the customer workbook
filter = "*.xl* (*.xls*),*.xls*"
caption = "Please Select file to import "
SourceF = Application.GetOpenFilename(filter, , caption)
Set SourceW = Application.Workbooks.Open(SourceF)
Set TargetS = TargetW.Sheets("AAR")
Set SourceS = SourceW.Worksheets("AAR")
Dim c As Range
Dim Tar As Range
Dim Src As Range
Set TargetS = ThisWorkbook.Worksheets("AAR")
If cmdSection.Value = "S1 Mobilization" Then
Set Tar = TargetS.Range("A5:A59")
Set Src = SourceS.Range("A5:A59")
ElseIf cmdSection.Value = "S1 Administration" Then
Set Tar = TargetS.Range("A63:A96")
Set Src = SourceS.Range("A63:A96")
ElseIf cmdSection.Value = "S1 DEMOB Individuals" Then
Set Tar = TargetS.Range("A102:A132")
Set Src = SourceS.Range("A102:A132")
ElseIf cmdSection.Value = "S1 DEMOB Units" Then
Set Tar = TargetS.Range("A137:A181")
Set Src = SourceS.Range("A137:A181")
ElseIf cmdSection.Value = "CRC" Then
Set Tar = TargetS.Range("A185:A234")
Set Src = SourceS.Range("A185:A234")
End If
For Each c In Tar ' Tar = Target Range defined above
Set myres = Src.Find(c.Value)
If Not myres Is Nothing Then
c.Offset(0, 2).Resize(1, 7).Value = myres.Offset(0, 2).Resize(1, 7).Value
End If
Next
ThisWorkbook.Sheets("AAR").Activate = True
SourceW.Close SaveChanges:=False
End Sub