Copy and Paste Loop Issue - One workbook to another

  • Hello all,

    I'm back again now with another annoying issue to do with loops (which, I'm slowly starting to understand but my mind is still being blown). Various tutorials have been looked at, the code below has been taken from Mr Excel and slightly modified but it still won't do exactly what I would like it to.


    I have a large spreadsheet of over 5,000 rows of data from columns A to T. This needs to be split across approximately 123 spreadsheets named after a specific nationwide region; Aberdeen City, Aberdeenshire, Angus etc (as shown in column C on the example attached).

    Using the code below, I can get it to loop through the data and copy a region to a worksheet within the current workbook fairly successfully. However, there are some annoying issues.


    Number 1. I can't work out how to copy the information to a completely different workbook, only worksheets within the master book. When trying to set a workbook range to a workbook on the server it keeps failing, either I can't type in the file path (red text of doom whatever string I try) or the code just fails. All the files will be stored on a server; A:\Month\Main Folder\Regions\[file name here.xlsx].

    Number 2. When running the code as it is, it copies the two regions currently added to worksheets contained within the workbook, however, for Abdereenshire, the data is pasted from row 19, when it should be row 2. What is wrong with the code to make this happen? I have tried varying j yet nothing changes......

    I would like this code to copy region X and paste in to region X's workbook stored on a server, then move on to region Z and copy to region Z's workbook and so on.

    I've tried to work this out myself but have lost a good few hours trying varying solutions yet nothing is working. It's time to farm this out for any advice I can get. Code is copied below, and spreadsheet attached, all tabs included and code has been run to see what I mean with issue number 2.

    Thank you in advance.

  • Try this. You need to set a reference to the other workbook. In your code your j variable was being used for every sheet and was not reset. The code below doesn't use such a variable anyway, but finds the first blank cell in column A.
    [VBA]Sub CopyRegions()

    Dim c As Range
    Dim Source As Worksheet
    Dim wbTarget As Workbook

    Set Source = ActiveWorkbook.Worksheets("Master Sheet")
    Set wbTarget = Workbooks.Open("insert name here")

    For Each c In Source.Range("C2:C58") 'Check through all rows on the master sheet based on column C
    Source.Rows(c.Row).Copy wbTarget.Sheets(c.Text).Range("A" & Rows.Count).End(xlUp)(2)
    Next c

    End Sub[/VBA]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!