Copy data from one workbook to another multiple times using filepaths

[greenhillchris]

Hi,

I have multiple workbooks which I need to copy the data over to different multiple workbooks. Each data in a workbook needs to be copied over to certain workbook and then saved and closed.

I am using specific filepaths for each workbook, so in my active workbook that I am going to run the macro from, column A has the source filepath for each workbook where the data is copied from and in column B the filepath of where the data from the source workbook should be pasted too.

I have started on the code as below but running into errors when the copying and pasting part comes into play and I still need to add the save and close code.

Granted this is probably the most backward way of doing it so was just looking for adivce on how to improve this code and to get it to work how i want it too.

Thanks

Public Sub TrackersCopyAndPaste()
Dim documents As Range, wb As Range 'Source
Dim documentsDest As Range, wbd As Range 'Destination
Dim ws As Worksheet


Set documents = ActiveSheet.Range("A2:A12") 'Source file paths
Set documentsDest = ActiveSheet.Range("B2:B12") 'Destination file paths


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

For Each wb In documents
    Workbooks.Open Filename:=wb 'source workbooks
Next
    
For Each wbd In documentsDest
    Workbooks.Open Filename:=wbd 'destination workbooks
Next

For Each ws In wb
wb.Range("A2:Y25").Copy 'source
wbd.Range("b2").Paste.Values 'destination
 
Next

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

End Sub

 

[Micron]

When it's a code error, always state the error number and description and what line causes it. That helps to focus responses.
My guess is, the problem arises because you've opened several workbooks and when code execution gets to
For Each ws In wb

it has no idea which one to look at.

 

[greenhillchris]

Hi, thanks for the reply. Yes the error is happening at that stage

For Each ws In wb

The error message is Run-time error 424 object required.

Ideally I would only to like it to open 1 source and destination workbook at the same time, copy, paste, save and close and then move on to the next set of workbooks

 

[Micron]

One way would be to loop over the rows in open wb and in each iteration:

Begin loop
- assign A1 value to strDocSource
- assign B1 value to strDocDest
- set application properties
- open strDocSource
- open strDocDest
- copy range "A2:Y25" from source to "b2" in dest
- save and close dest
- close source
loop

reset application properties

You can't just loop over a bunch of wb's and open them and expect code to know which one you want to deal with at any particular time. You'd have to refer to each one in each group by name (or by index number, which could be haphazard). BTW, wb is commonly used to denote a workbook object variable. Using it as a range object variable makes for some confusion. In my previous reply, I made that mistake. I see that you were looping over a range, but it wouldn't change the fact that the object reference couldn't be distinguished.

 

[greenhillchris]

Thanks, this makes sense.

I have tried to do what you have said but struggling to piece this together.

Below is my updated code but I can’t get it to copy from the source file, it copies and paste only to the destination file.

If I expand the range for strDocSource and strDocDest at the top to incorporate all my files range I get run time error 13 Type mismatch.

Sorry but maybe I am missing something or I have not assigned things properly.

Public Sub TrackersCopyAndPaste2()

Dim strDocSource As Range
Dim strDocDest As Range

Set strDocSource = ActiveSheet.Range("A2") 'Source file paths
Set strDocDest = ActiveSheet.Range("B2")  'Destination file paths


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

For Each Workbook In strDocSource

Workbooks.Open Filename:=strDocSource
Workbooks.Open Filename:=strDocDest
Range("A2:Y25").Copy 'source
Range("b3").PasteSpecial Paste:=xlPasteValues 'destination
ActiveWorkbook.Close SaveChanges:=True


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

End Sub

 

[Micron]

Workbooks.Open Filename:=strDocSource

I'm surprised that works at all given that you specify a range object as the file name but then Excel vba is not really my thing (Access vba is). Trying to expand my knowledge by helping out. Unfortunately I have no code for this. My last suggestion probably wasn't clear. When I said 'assign A1 value' I meant the cell value, which I assume is the full path, so the variable would be a string, not an object. Sorry I have to leave this for probably the rest of the day, but perhaps someone else can whip up something for you. If not, maybe do more searching in the meantime, for code that more closely fits what you're doing. Consider posting what your path values look like, but use xl2BB from posting toolbar?

 

[greenhillchris]

That’s ok, thanks for all your help, you have given me more of an understanding how the code should be. I’ll keep playing around with it.

 

[Micron]

Post back if you remain stuck and I'll see if I have the time to dig in.