i like to copy table body of a 2 column table in a workbook to the first 2 columns of a 8 column table in another workbook. i wrote this code but when pasting the body to the other table i get the 2 columns repeated on column 3 and 4, 5 and 6 and 7 and 8
Dim wbk As Workbook
Sub overzetten_naar_planning()
Dim folderPath As String, fileName As String, filePath As String
Dim LastRow As Variant
Dim Wb As Workbook
Set Wb = ThisWorkbook
' create path containing the planning file
folderPath = ThisWorkbook.Path & "\"
fileName = "6s planning 2015.xlsx"
filePath = folderPath & fileName
' check if planning is already open in your session.
If IsWorkBookOpen(filePath) Then
Set Wba = Workbooks(fileName)
Else
Set Wba = Workbooks.Open(filePath, UpdateLinks:=0)
End If
Wba.Activate
Set LastRow = ActiveSheet.ListObjects("Planning6S").ListRows.Add
ThisWorkbook.Activate
ActiveSheet.ListObjects("WerkplaatsTabel").DataBodyRange.Copy
LastRow.Range.PasteSpecial xlPasteValues
End Sub
Function IsWorkBookOpen(fileName As String)
Dim ff As Long, ErrNo As Long
On Error Resume Next
ff = FreeFile()
Open fileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0
Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function
Aucun commentaire:
Enregistrer un commentaire