Dear all,

Let me start by saying that I'm no VBA expert at all and that what little I know, I've learned through trial and error. I do like to learn and understand VBA better so that I can be completely self-sufficient, but sometimes I'm still in need of some help. Any help you can offer me is very much appreciated and will add to my modest knowledge of VBA, so thank you in advance for that.

I've looked for similar posts on numerous excel forums but have found nothing that meets my exact needs. All the information that I found matched some specific part of my question, and I've copied and tweaked those parts dilligently, but the current macro as a whole seems to be flawed.

My goal is to have a destination workbook that collects ranges of data from different (dozens of) source workbooks. In every source workbook there's a range that I need to find, which is marked with "[Body Start]" at the beginning and "[Body End]" at the end. That much I've figured out so far and it works (for your information all these source workbooks consist of only one page and only the number of rows is dynamic, columns stay the same).

Having completed the "data finder" I now have to figure out how to let VBA copy this newly found range from its source workbook and paste it into its destination workbook. That's were the (current) problem lies. For some reason VBA won't copy the specified range (named "ClaireCopy" in the code) to the destination workbook. I've tested these components seperately and they seem to work, meaning that I've tested if VBA names the specified range correctly (it does), and if the "connection" between workbooks functions properly (it does). For some reason however the problem lies in the range. Maybe I haven't defined my variables and ranges correctly? Maybe I completely missed some crucial structural part? I don't know.

I'm sure that whatever the problem is, it's due to my lack of knowledge of VBA and that it's not a major issue. My modest knowledge of programming leads me to think I'm failing to get the structure of these kinds of subs in VBA correctly and I'm missing some procedural parts in my code. But that's just a guess. PLEASE NOTE THAT I ONLY NEED HELP ON THE COPY/PASTE RANGE PROBLEM. I AM AWARE THAT A LOT OF WORK WILL COME AFTER THAT HAS BEEN RESOLVED, BUT IT'S MY INTENTION TO DO THAT MYSELF. FOR NOW I WOULD JUST LIKE TO GET THIS PART OUT OF THE WAY.

You will find 2 subs in below code:
1. Finding the range
2. Transferring it from source to destination

Thanks in advance for your help. Please find below my code so far:


Sub Data_search()

Dim ClaireCopy As Range

For i = 1 To 200
For j = 1 To 200

If Cells(i, 1).Value = "[Body Start]" And Cells(j + 1, 1).Value = "[Body End]" Then
Range(Cells(i + 1, 1), Cells(j, 109)).Select
ActiveWorkbook.Names.Add Name:="ClaireCopy", RefersTo:=Selection
End If

Next j
Next i


End Sub



Sub Workbook_connection()

Dim DestBook As Workbook, SrcBook As Workbook
Dim ClaireCopy As Range

Application.ScreenUpdating = False

Set SrcBook = Workbooks.Open("C:\Users\xxx\sourcefile1.xlsx")
Set DestBook = Workbooks.Open("C:\Users\xxx\outputfile1.xlsx")

On Error Resume Next

Data_search

With SrcBook.Worksheets(1)
.Range("ClaireCopy").Copy
End With

With DestBook.Worksheets(1)
.Range("A1").Paste
End With

DestBook.Save
DestBook.Close
SrcBook.Save
SrcBook.Close

On Error GoTo 0

Set DestBook = Nothing
Set SrcBook = Nothing


End Sub