Copy and paste from one WB to another multiple times using file paths

[Greenhillchris]

Hi
Looking for some advice on my vba code and why I am getting errors.

What I am trying to do is open an excel workbook and copy and paste that information into another workbook and repeat that multiple times for different workbooks using direct file paths.

I have the file paths of the workbooks I want to open and copy data from in column B. In column C I have the file paths to open that workbook and paste the data in. Example: file path in B2 & C2 would open and data copied from file path B2 to C2.

Here’s my code

>>> use code - tags <<<

Public Sub OpenCopyClose()

Dim WB1 As Range, WB2 As Range
Dim ws1 As Worksheet, ws2 As Worksheet

Set WB1 = ActiveSheet.Range("B2", ActiveSheet.Range("B2").End(xlDown)) 'Source file paths
Set WB2 = ActiveSheet.Range("C2", ActiveSheet.Range("C2").End(xlDown))  'Destination file paths
Set ws1 = WB1.Sheets("Tracker") ' Source name of Sheet
Set ws2 = WB2.Sheets("Tracker") 'Desination name of Sheet


With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .EnableEvents = False
    .AskToUpdateLinks = False
End With

For Each Workbook In WB1

Workbooks.Open Filename:=WB1
Workbooks.Open Filename:=WB2
ws1.Range("b6:Y25").Copy 'source
ws2.Range("B").PasteSpecial xlPasteValues 'destination
WB1.Close SaveChanges:=True
WB2.Close SaveChanges:=True


  Next

  With Application

    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
    .AskToUpdateLinks = True
End With

End Sub

I am getting an error of runtime error 438 object doesn’t support this property or method at this stage and wondering why

Set ws1 = WB1.Sheets("Tracker") ' Source name of Sheet

Thanks

 

[vletm]

Greenhillchris
What is Your verified WB1-value?

 

[Greenhillchris]

Greenhillchris
What is Your verified WB1-value?

Thanks for the reply but I am sorry not quite sure what you mean by that

 

[vletm]

Have You use Watches to see / to verify - what is WB1's -value?
That You could later Open WB1... copy something from it and later Close it.

Please reread Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

How to get the Best Results at Chandoo.org
For the best/fastest results, Upload a Sample File
... with it, others could see/test those.

 

[Greenhillchris]

Thanks, I wasn’t aware of the watches window, WB1 value is showing as no value.

 

[vletm]

Greenhillchris
You could test this sample ...
I do these 'my way'.
I couldn't verify this at all (missing some files).
There is at least one detail - which You should verify ( row number ) before You'll test it.
... remember to have backups ...

 

[Greenhillchris]

Hi vletm

This works perfectly, thank you very much.

The code is lot simpler now too which is great.

You were spot on with the row number and good point about why save the source WB when closing.

Thanks again for your help