I have written a large macro to take information across a variable number of rows and columns, copy each set of rows to a new appropriately named worksheet, then copy the values on the new worksheet so that the master list then contains links to the sub-worksheets. So, in other words, assume a file with 100 records comprised of 5 companies with 20 records per company. My macro creates 5 new worksheets - each with the name of the company. The information from the original worksheet is pasted into the respective worksheets. But then it is copied back to the original master spreadsheet as links. So if anyone updates anything on the 5 individual company spreadsheets it will automatically be saved to the master. Please note that this macro works just fine if I just do a simple pasting of values instead of a paste link.
Below is my code. It works until it seems to poop out after x no. of records. [I have also attached the Excel file as well.]
I tried a few ideas I found on the web such as disabling GoToMyPC or gotoassist - but I do not think I have either on my machine. I also experimented a bit with removing Select statements as well. I admit to being rusty with the VBA programming. Any suggestions are welcome and appreciated. Happy to be a part of the forum.
Thank you.
Zach Feinstein
zfeinstein@isgmn.com
<Code below>
Sheets("Sheet1").Select
ActiveSheet.Range(Cells(RowNum, 1), Cells(RowNum, LastCol + (Attempt * 3))).Select
Selection.Copy
Sheets(Left(CoName, 30)).Select
Range("A1").Offset(CoRowNum - 1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Range(Cells(RowNum, 1), Cells(RowNum, LastCol + (Attempt * 3))).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
CoRowNum = CoRowNum + 1
RowNum = RowNum + 1
Loop
Sheets("Sheet1").Select
Range("A1").Select
Bookmarks