Run Time Error 1004, Error 13 & Error 424 for the same thing... Newbee Help Please!!

[amisagi]

Hi, I am trying to write a code which will
loop through all excel workbooks in a particular folder &
copy data from a specific tab on each sheet and
then paste it to a master excel workbook.

I have got the code to select folder, extension, tabname basis inputs from user and also loop thru all the files in the folder.
However, when i try to target to a specific tab & copy its contents i start getting error 1004 or 424 depending on what i write. Perhaps i am doing something fundamentally wrong...
it would be highly appreciated if someone could please help

Variables declared:

Dim myFolderPath As String
Dim myFilename As String
Dim myExtension As String
Dim myTabName As String
Dim FldrPicker As FileDialog
Dim i As Integer
Dim thisWB As Workbook
Dim runningWB As Workbook
Dim WSheet As Worksheets

Part where the problem code is:-
Line in Red gives error 424

Do While myFilename <> ""
'Set variable equal to opened workbook
Set runningWB = Workbooks.Open(myFolderPath & myFilename)

runningWB.Activate
ActiveWorkbook.Worksheets(myTabName).Range("A1", Range("A" & Row.Count).End(xlUp)).Copy
...
...
Loop


Same section, i tried to do a bit differently, and got error 13!
Line in Red gives error 13

Do While myFilename <> ""
'Set variable equal to opened workbook
Set runningWB = Workbooks.Open(myFolderPath & myFilename)

runningWB.Activate
Set WSheet = ActiveWorkbook.Worksheets(myTabName)
WSheet.Range("A1").Select
Range(ActiveCell, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
...
...
Loop

Same section, another approach, error 1004!
Line in Red gives error 1004

Do While myFilename <> ""
'Set variable equal to opened workbook
Set runningWB = Workbooks.Open(myFolderPath & myFilename)

runningWB.Activate
ActiveWorkbook.Worksheets(myTabName).Range("A1").Select
Range(ActiveCell, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy

...
...
Loop

Please help... i am kinda at my wits end...

let me know if any other detail is required...

 

[Michael M]

You haven't assigned anything to the variable "Mytabname", you've only dimmed it !!

 

[Domenic]

Also, you'll need to replace...

Row.Count

with

Row[COLOR=#ff0000]s[/COLOR].Count

Although, you should qualify your references...

[color=darkblue]With[/color] ActiveWorkbook.Worksheets(myTabName)
    .Range("A1", .Range("A" & .Rows.Count).End(xlUp)).Copy
End [color=darkblue]With[/color]

Hope this helps!

 

[amisagi]

Thank you Michael & Domenic!!
I had lost my password so couldnt reply earlier... please accept my apologies for appearing to be an ungrateful a**

 

[Michael M]

No worries ....glad we could help...

 

[Domenic]

Yep, no problem... cheers!