Quote Originally Posted by jinnlau View Post
Hi Paul,

1. I have given a screenshot of what the input workbook and the master list looks like.

2. "Your approach is not very general purpose, and I think the overall structure needs improvement" Sorry I am new to VBA not sure what you mean by not general purpose. Can you provide insights as to how I can improve the structure?

3. Oh sorry I didn't know how to debug but I have just found out how to. The error is coming from this line: Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
I have changed my percentagecollected to long as well.


1. There's no way to test a screen shot

2.
However the problem is that every week the excel column change and I have to only paste 3 specific columns. So how can I make this dynamic? The method I have decided to adopt is to use userform, where the user can specify what column range and row to copy their data from (as shown in the image).
2. By 'general purpose' I meant including in the macro some identifying information for the columns to be copied and logic as to where they are supposed to go so that you don't have to rely on a userform or a user


3. There could be any number of reasons why the line fails. From this piece of your code, it appears that the macro is in a third workbook, and opens two more. Correct?

[CODE]
Set monthlytransferdata = Workbooks.Open("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Transfer Monthly Data.xlsb")

Worksheets("Email_AR NTB (3)").Select
percentagecollected = Range("H:H")

Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")[
/CODE]