VBA referring to a different sheet, automatically fill cell based on content of other cell

The problem was that you had the macros in the worksheet module instead of a regular module. I moved the macros into Module3 and changed the 'Copy Data' button link to the new location of the macro. Your 'copydata' macro now runs and then calls the 'FillColumnsitbof' so both operations are run one after the other. If you want to run them separately, delete the line:

Call FillColumnsitbof

in the 'copydata' macro and assign the 'FillColumnsitbof' macro to a separate button. The attached file has all the changes. Hopefully, everything is OK now.