Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defined error)

[winz]

Hi am new to VBA, Could you please help on the below code.



I have 2 workbooks and i want to copy the columns from one to another with the matching header.



It shows an error in the following line as (Runtime Error - 1004 --> Application-defined or object Defined error)

tWB.sheets("Main").Range(Cells(TLRow, TCol)) = aWB.sheets(1).Range(Cells(2, SCol), Cells(SLRow, SCol))

Sub Pull ()



Dim FileName() as Variant, nw as integer, i as integer

Dim tWB as Workbook, aWB as Workbook

Dim hcell as Range, Header as Range

Dim SCol as Integer, TCol as Integer, SLRow as Integer, TLRow as Integer, SIndex as Integer,

Dim TIndex as Integer



Set tWB = ThisWorkbook

FileName = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls; *.xlsm),*.xls;*.xlsm", MultiSelect:=True)

nw = UBound(FileName)



Set Header = tWB.sheets("List").Range("A1:A" & tWB.Sheets("List").Range("A" & Rows.Count).End(xlUp).Row)



For i = 1 to nw

     Workbooks.Open FileName(i)

     Set aWB = ActiveWorkbook

         For each hcell in Header

             SCol = Application.Match(hcell.value, aWB.Sheets(1).Rows(1),0)

             TCol = Application.Match(hcell.Offset(0,1).value, tWB.Sheets("Main").Rows(1),0)

             SLRow = aWB.Sheets(1).Cells(aWB.Sheets(1).Rows.Count, "A").End(xlUP).Row
             TLRow = tWB.Sheets("Main").Cells(tWB.Sheets(1).Rows.Count, TCol).End(xlUP).Row + 1
             SIndex = Split(aWB.sheets(1).cells(1,SCol).Address, "$")(1)

             TIndex = Split(tWB.sheets("Main").cells(1,TCol).Address, "$")(1)


             tWB.sheets("Main").Range(Cells(TLRow, TCol)) = aWB.sheets(1).Range(Cells(2, SCol), Cells(SLRow, SCol))
 
         Next hcell

Next i

End Sub

Another Method:

Also i have tried to convert the column index number to Letter but am not sure what the error is since the code shows no error but the output is not coming

tWB.Sheets("Main").Range(TIndex & TLRow) = aWB.sheets(1).Range((SIndex & 2), Range(SIndex & SLRow))

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defined error)

Could you please help on the below code. I have 2 workbooks and i want to copy the columns from one to another with the matching header. It shows an error in the following line as (Runtime Error - ...

stackoverflow.com

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[RoryA]

Also here:

Range with Cells Function (Runtime Error - 1004 --> Application-defined or object Defined

Hi am new to VBA, Could you please help on the below code. I have 2 workbooks and i want to copy the columns from one to another with the matching header. It shows an error in the following line as (Runtime Error - 1004 --> Application-defined or object Defined error)

www.excelforum.com

 

[winz]

HI Rory.. Thank you so much.. It Worked!!! Going Forward I Will post the links here..