Copy Range from Workbook2 to Workbook Problem

Dear Sirs,

I've very much frustrated with the copy and paste problem, as I've been trying different ways but still fails :

1. Every month, I've to copy and paste a row of forecast data from a number of files (source) to a master file (target).
2. Both source and target file have the same password locking sheet1, and sheet1's Tab name will change every month
3. Since the Master file and Souce file will change file names from time to time, my macro has to use Workbook(1) as Master file (first open file) and Workbook(2) as Source file (second open file)

My codes are :

Please Login or Register  to view this content.

The codes can finish to the end, but cannot see the pasted result (ie just nothing copied to the Master file)
It seems the password protection may cause the issue, but do not know how to modify.

Hope someone could kindly advise.

Many thanks indeed.

Originally Posted by chasoe

my macro has to use Workbook(1)

Hello. Can you attach the file you are copying from? The original is optional, the main thing is that the structure should be identical. Or is it identical to the Receipt Forecast -MasterFile.xlsm‎ that you attached to your Thread?

HI everybody,

I now re-upload the Master file and the Source file.

My intended procedure is :
1. Open Master file (destination file-workbook1), then the Source file (workbook2), both files are identical in structure, and "sheet1" code sheet name refer the same first sheet in the 2 files.
2. Place cursor in Source file (sheet1) on any cell in the project row with forecast data, and execute the copy forecast macro
3. The macro will first get the project code in column A of Source file (copy the whole row), and then lookup the corresponding project code row in Master file for pasting by formula
4. Since workbook1 & workbook2, and also sheet1 code sheet name, will change each month, the codes cannot use the actual file name and sheet name
5. To avoid the Source file being tampered with in sheet structure, password protection is required

Perhaps I'm not properly trained in progamming (the codes are often built up by looking up the internet), there should have been some syntax error in the codes which throw up errors again and again.
Errors occur include not recognizing the use of "sheet1", or stuck while pasting, or nothing being pasted after finishing the codes(may be due to "display alert = false"

So it looks to me a simple operation of copy and paste, but turn out a difficuilt task for me.

See if anyone could help spot out the problems in the codes.

Many thanks again.

Originally Posted by PCI

What about the Special Paste ... it seems you want to copy-paste only values is it ??

I need to paste formulas, because some cells have to retain cell formulas linking adjacent cells.

The error in JVList should not be the cause in copy and paste error.

To dispose this doubt, I remove all the range names errors as well as removing any formula linkages to JVList, and upload the files again.

Let me take some time to test.

Hello @chasoe.
What would you say?

Please Login or Register  to view this content.

Hello,

I've tested both revised codes from @PCI and @MikeVol, both work fine.

Since there are advanced codes which I cannot fully understand, so there may be problem in future if modification is required due to changes in the file structure, although I hope I can find time to learn more.

Anyway, many thanks indeed for your untiring efforts.