Sub Tabellen_zusammenfuehren()
Dim rngCsM As Excel.Range
Dim rngCM As Excel.Range
Dim rngCS As Excel.Range
Dim lngOffset As Long
Dim feed, feedsheet As Worksheet
Dim zielSheet As Worksheet
Dim w, x, y, z As Long
Dim linkTarget, linkFeed As String
Dim main_link_target, image, beschreibung, g_d_preis, price, main_link_source, image_source, description, preis, link_2 As Integer
Dim Zeile As Long
Dim ZeileMax As Long
lngOffset = 1
Set zielSheet = Worksheets("Main")
With Worksheets("Main")
Set rngCsM = .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft))
End With
For Each wks In ActiveWorkbook.Worksheets
If Not wks.Name = "Main" Then
With wks
For Each rngCM In rngCsM.Cells
Set rngCS = .Rows(1).Find(What:=rngCM, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
If Not rngCS Is Nothing Then
Debug.Print "Spalte '"; CStr(rngCM); "' gefunden"
Set rngCS = .Range(rngCS.Offset(1), .Cells(.Rows.Count, rngCS.Column).End(xlUp))
anzahluebertragenezeilen = rngCS.Rows.Count
rngCM.Offset(lngOffset).Resize(rngCS.Rows.Count).Value = rngCS.Value
Else
Debug.Print "Spalte '"; CStr(rngCM); "' nicht gefunden"
End If
Next
lngOffset = lngOffset + anzahluebertragenezeilen
End With
End If
Next
feed = Application.GetOpenFilename
If feed <> False Then
Set feedsheet = Workbooks.Open(feed).Sheets(1)
Else
Exit Sub
End If
For z = 1 To feedsheet.Cells(1, 256).End(xlToLeft).Column
Select Case feedsheet.Cells(1, z)
Case "link_2"
link_2= z
Case "main_link_source"
main_link_source = z
Case "image_source"
image_source = z
Case "description"
description = z
Case "Preis"
preis = z
End Select
Next z
With zielSheet
For y = 1 To rngCsM.Columns.Count
Select Case .Cells(1, y)
Case "N_Link_1"
N_Link_1 = y
Case "main_link_target"
main_link_target = y
Case "Product"
Product = y
Case "Image"
image = y
Case "Beschreibung"
beschreibung = y
Case "G_D_Preis"
g_d_preis = y
Case "Price"
price = y
End Select
Next y
ZeileMax = .UsedRange.Rows.Count
For Zeile = ZeileMax To 1 Step -1
If Trim(.Cells(Zeile, 1).Value) = "" Then
.Rows(Zeile).Delete
End If
Next Zeile
For x = 2 To .UsedRange.SpecialCells(xlCellTypeLastCell).Row
If .Cells(x, 1).Value <> "" Then
linkTarget = LTrim(.Cells(x, N_Link_1).Value2)
For w = 2 To feedsheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
linkFeed = LTrim(feedsheet.Cells(w, link_2).Value2)
Debug.Print linkTarget
Debug.Print linkFeed & " =" & linkTarget = linkFeed
If linkFeed = linkTarget Then
.Cells(x, main_link_target) = feedsheet.Cells(w, main_link_source)
.Cells(x, image) = feedsheet.Cells(w, image_source)
.Cells(x, beschreibung) = feedsheet.Cells(w, description)
.Cells(x, g_d_preis) = feedsheet.Cells(w, preis)
.Cells(x, price) = feedsheet.Cells(w, preis)
End If
Next w
End If
Next x
End With
End Sub