Copy from workbook to workbook wlth VBA

[rspalding]

I need help copying from 1 workbook to another. I want this code to read cell C1 for the wokbook name to open and then copy a list of cells from sheet 1 into the same cells sheet 1 in a different workbook identified by C5.

Thanks for your help,

Robert

 

[rspalding]

John,

What is the proper code get it to see the sheet named "Estimate"? Also, how can I get to say yes on the update when opening the retrieved workbook?

 

[Datsmart]

This question still needs to be answered:
What do you have in cells B1, B2, and B3 of the worksheet named "Main"?
That is where your code is picking up the name for the Path of the workbook to open. (B1)
The workbook name. (B2)
The Worksheet name to copy to. (B3)

Nories best guess at what you have on your page, (see thread #15), suggests you have the worksheet name of "Estimate" for both your Source and Target workbooks listed in cells B4 and D4 of a worksheet. Your code assumes that the name of that worksheet is "Main". You have not let us know if this is true or not.

Your code also sets the name of the Source workbook to "ActiveSheet.Name". That will be whatever worksheet was active the last time the workbook was opened.
If you want to use what you have in cell D4 as the source worksheet, change your code like this for that variable:

SourceWS = Sheets("Main").Range("D4")

As far as eliminating the update prompt, try adding code lines like this:

'Turn Alerts OFF
Application.DisplayAlerts = False

'Your code here...

'Turn Alerts back ON
Application.DisplayAlerts = True

 

[rspalding]

John,

B1, B2 and B3 are the location, target and destination. I still need to direct to the proper workshhet on the workbook being opened. There are multiple sheets and it needs to be deirected to "Estimate". I tried ActiveSheet = ("estimate") but it didn't work.

Robert

 

[rspalding]

John,

Do I change this code line: SourceWS = ActiveSheet.Name

 

[Datsmart]

What is "location", a path, workbook name, worksheet name?
Wouldn't "target" and "destination" be the same thing? Maybe one is the workbook name and the other the worksheet name.
That is why I have asked several times, "What do you have in cells B1, B2, and B3 of the worksheet named "Main"?"

You do not want to use "SourceWS = ActiveSheet.Name", as that will copy from the worksheet that was the last sheet the workbook was open to when it was last saved.

Make sure your source worksheet variable is assigned like this:

SourceWB = Sheets("Main").Range("D4")

Where cell D4 contains the text "Estimate". If the source sheetname is in a different cell, adjust the code accordingly.

 

[rspalding]

John,

Sorry that I didn't e-mail you back yesterday to tell you all is working except for the "Turn off Alerts". This is actually an Update Links which I want to do.
If you could give me some diection I would appreciate it.

Thanks,

Robert

 

[Datsmart]

Did you try adding the code lines I mentioned in post #22?

 

[rspalding]

John,

Here is the code i added and it did what was needed.

With Workbooks.Open(SourcePath & "\" & SourceWB, False)

.Close , False

No update to links or when closing the workbook.

Thanks for the help. I'm sure I'll have more questions later.

Robert