I have two workbooks. workbook1 is my destination workbook(DWB) and workbook 2 is my original workbook (OWB). My OWB is in the network folder.
I am trying to copy all the contents of OWB to DWB.
Below is my code I have tried so far. But in the line below, I get a error
object doesnot support this property
Set OWB = Workbooks.Open(Filename:=filepath)
Sub Extract()
Dim DWB As Workbook
Dim OWB As Workbook
Dim path1 As String
Dim path2 As String
Dim filepath As String
Dim LastRow As Long
Dim i As Long
Dim Lastcol As Long
Dim header As String
Dim cell As Range
Set DWB = ThisWorkbook
path1 = DWB.Path
filepath = "\\cw.wan.com\root" & "\Loc\04_Infol\pivot.xlsx"
Set OWB = Workbooks.Open(Filename:=filepath)
LastRow = OWB.Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Lastcol = OWB.Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To Lastcol
header = OWB.Worksheets(1).cell(1, i).Value
With DWB.Worksheets("T").Range("A4:Y4")
Set cell = .Find(header, LookIn:=xlValues)
End With
If Not cell Is Nothing Then
OWB.Worksheets(1).Range(Cells(2, i), Cells(LastRow, i)).Copy Destination:=DWB.Worksheets("T").Cells(5, cell.Column)
Else
'handle error
End If
Next i
OWB.Close savechanges:=Fasle
End Sub
MsgBox filepath
before the error, copy exactly what you see to your Windows Explorer and press Enter, what would happen? – Vityataheader = OWB.Worksheets(1).cell(1, i).Value
as it should beCells
and notCell
. – Rory