Copying A Sheet from One Workbook to Another with VBA

[UncleBajubjubs]

Hello, I have a workbook, "Purchasing Workbook", which has a worksheet called "Vendor Data" on which a list of parts is manually entered in, then sent to whomever to purchase the parts. The parts are determined by a different workbook "Data Workbook", and the information is then manually entered into the "Vendor Data" worksheet in "Purchasing Workbook".


To make it so the information doesn't have to be manually entered after it's been calculated, I copied the "Vendor Data" worksheet into "Data Workbook", and then had all the cells auto-fill with the correct parts information. My goal is that after "Vendor Data" is completely filled in on the "Data Workbook", the user presses a button in the "Data Workbook" to copy it over to "Purchasing Workbook" either directly on the sheet, or it makes a new sheet and the old one is deleted.


As both "Purchasing Workbook" and "Data Workbook" are templates that will be saved as new documents with the name of the project they are for, I had to write the code to open a dialog box allow the user to pick which file to open and copy the data to.


I wrote code to do this, but didn't realize "Purchasing Workbook" is a 1997-2003 document, and "Data Workbook" is the newest version. Trying to copy the sheet gives the error "Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use Copy and Paste commands to insert it into the sheets of another workbook.".


So now I'm stuck again. Is there another way to do this? The data on the worksheet is entirely contained through A1-AJ191, so I'm thinking there's a way to just copy that range, but don't know exactly how I'd code it.

Thanks.

 

[UncleBajubjubs]

Thanks again, I ran that and got the "File Error" message, so I added a msgbox line between the other lines to see which was giving it the error.

<code>
Workbooks(FileNm.Name).Sheets("Vendor Data").Range("A1").PasteSpecial xlPasteValues
</code>
Is the line that is giving it trouble, I'm looking at it but I don't see anything wrong.

 

[NdNoviceHlp]

U changed the sheet names. So...

ThisWorkbook.Sheets("Vendor Data Sheets").Range("A1:AJ191").Copy
Workbooks(FileNm.Name).Sheets("VENDOR COMP DATA").Range("A1").PasteSpecial xlPasteValues

Dave

 

[UncleBajubjubs]

I had corrected the sheet names, I was just copying from what you had written rather than my VBA. It's definitely copying the data correctly, as after it gives the file error message, the proper range is still selected.

 

[NdNoviceHlp]

I tested the code before I posted and it worked. I have re-tested the code and it works. Trial opening a new workbook and pasting in the code. Change all the sheet names in the code to "Sheet1". Run the sub and select a wb that has a sheet1 available and transfer the data. It works. It seems that your sheet naming is still funky. You can trial commenting out this line of code to find out where the error actually is. Dave

'On Error GoTo below

 

[UncleBajubjubs]

Alrighty thank you, so I did as you suggested and I can indeed copy the page to a new workbook, and then I commented out the error line and found that
<code>


Workbooks(FileNm.Name).Sheets("VENDOR COMP DATA").Range("A1").PasteSpecial xlPasteValues

</code>

which gave me the error message : "This operation requires the merged cells to be identically sized."

I'm thinking I'll have to unmerge all of cells before copying, and then remerge them after they've pasted, unless there is an easier way.

 

[NdNoviceHlp]

Merged cells were never mentioned and I have no idea what effect they have on copying and pasting. Have to search Google to find out. Dave