Ik ben niet zo onderlegd in macro's, maar ik heb alvast de onderstaande kunnen samenstellen.
Deze moet excel bestanden binnen één werkmap samenvoegen in één worksheet.
Deze werkt goed, maar ik wens nog 2 dingen te wijzigen
- dat enkel de rijen (uit de diverse bestanden) worden gekopieerd waar de waarde in kolom A "1" is
- dat enkel de waarden worden gekopieerd en niet de formules
Sub MergeUsedRanges()
Dim wbSource As Workbook, wbTarget As Workbook
Dim shTarget As Worksheet
Dim sPath As String, sFileMask As String, sTargetFile As String
Dim sFileName As String
Dim bIncludeHeaders As Boolean, lTargetRow As Long, r As Range
sPath = ActiveWorkbook.Path
sFileMask = "*.xls"
sPath = sPath & "\"
sFileName = Dir(sPath & sFileMask)
If sFileName <> "" Then
Set wbTarget = Workbooks.Add
Set shTarget = wbTarget.Worksheets(1)
End If
lTargetRow = 2
Do Until sFileName = ""
Set wbSource = Workbooks.Open(Filename:=sPath & sFileName)
Set r = wbSource.Sheets(1).UsedRange
If r.Rows.Count > 0 Then
If bIncludeHeaders = True Then
bIncludeHeaders = False
Else
Set r = r.Offset(4, 0).Resize(24 - 4, r.Columns.Count)
End If
r.Copy Destination:=shTarget.Cells(lTargetRow, 2)
lTargetRow = lTargetRow + r.Rows.Count
End If
wbSource.Close
sFileName = Dir
Loop
Application.DisplayAlerts = False
wbTarget.SaveAs Filename:=sPath & sTargetFile
Application.DisplayAlerts = True
wbTarget.Close
End Sub
bedankt
Deze moet excel bestanden binnen één werkmap samenvoegen in één worksheet.
Deze werkt goed, maar ik wens nog 2 dingen te wijzigen
- dat enkel de rijen (uit de diverse bestanden) worden gekopieerd waar de waarde in kolom A "1" is
- dat enkel de waarden worden gekopieerd en niet de formules
Sub MergeUsedRanges()
Dim wbSource As Workbook, wbTarget As Workbook
Dim shTarget As Worksheet
Dim sPath As String, sFileMask As String, sTargetFile As String
Dim sFileName As String
Dim bIncludeHeaders As Boolean, lTargetRow As Long, r As Range
sPath = ActiveWorkbook.Path
sFileMask = "*.xls"
sPath = sPath & "\"
sFileName = Dir(sPath & sFileMask)
If sFileName <> "" Then
Set wbTarget = Workbooks.Add
Set shTarget = wbTarget.Worksheets(1)
End If
lTargetRow = 2
Do Until sFileName = ""
Set wbSource = Workbooks.Open(Filename:=sPath & sFileName)
Set r = wbSource.Sheets(1).UsedRange
If r.Rows.Count > 0 Then
If bIncludeHeaders = True Then
bIncludeHeaders = False
Else
Set r = r.Offset(4, 0).Resize(24 - 4, r.Columns.Count)
End If
r.Copy Destination:=shTarget.Cells(lTargetRow, 2)
lTargetRow = lTargetRow + r.Rows.Count
End If
wbSource.Close
sFileName = Dir
Loop
Application.DisplayAlerts = False
wbTarget.SaveAs Filename:=sPath & sTargetFile
Application.DisplayAlerts = True
wbTarget.Close
End Sub
bedankt