mercredi 25 mars 2015

copy and paste data from excel table with 2 colums to excel table with 8 columns using VBA

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