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.
Sub CopyRegions() Dim c As Range Dim j As Integer Dim Source As Worksheet Dim Target As Worksheet 'ideally should be a seperate workbook on a server Dim Target1 As Worksheet ' ideally shoul be a seperate workbook on a server Set Source = ActiveWorkbook.Worksheets("Master Sheet") Set Target = ActiveWorkbook.Worksheets("Aberdeen City") 'how to set this to a variable book/sheet based on C? Set Target1 = ActiveWorkbook.Worksheets("Aberdeenshire") 'how to set this to a variable book/sheet based on C? j = 2 ' Start copying to row 2 in target book/sheet For Each c In Source.Range("C1:C58") 'Check through all rows on the master sheet based on column C If c = "Aberdeen City" Then Source.Rows(c.Row).Copy Target.Rows(j) j = j + 1 End If If c = "Aberdeenshire" Then Source.Rows(c.Row).Copy Target1.Rows(j) j = j + 1 End If Next c 'could be a line for each area? End Sub