After copying cells to another workbook the contents does not show up as expected.

[msmith1955]

Sorry I am new to VBA.
I have a test that runs on a device. The results of the test are written to an excel spreadsheet. Each device tested has its own workbook. The sheet from the needed data is opened then the macro is ran by selecting the button. My problem is that my code opens the file I choose, MLTEST, but it does not copy the contents of the file. Instead it copies what is in the quotes from the Macro that I ran and saved. I have changed the macro to include srtFN instead of the original file name.
To help I have 3 files that have information in them so I can test my macro by using the button. The button allows me to choose the file I want to add to the list. The macro writes to file MLTEST(G,17) if it is empty. It keeps searching until a cell in row 17 is empty.
What do I need to do to my code to get the contents into the new location? I know this is simple but I have tried to add a .value to multiple places including after the strFN. If I keep the original filename in the part of the macro that I ran and saved it works except that I cannot choose the file. It keeps filling in the data with the same file. I have tried this using the Absolute method and the relative method of recording.
Following is my code:

Please Login or Register  to view this content.

Any help would be appreciated. Thanks to all. Again I am new to VBA and using Forums.
Sorry I forgot to put down that I was using Excel 2003.

[xladept]

You need another equal sign:

Please Login or Register  to view this content.

[msmith1955]

I will try this and let you know. Thank you.

[msmith1955]

xladept I got an error: Run-time error '-2147417878(80010108)':
Method 'FormulaR1C1'of object 'Range' failed

This is what debug highlighted:
ActiveCell.FormulaR1C1 = "=strFN!SerialNumber"
I tried this again with the same results except that I noticed a 0 in the first cell I am sending
the data to.

[xladept]

Hi M,

I'm confused. Is "SerialNumber" a named range? Is there a certain File name that you must pick?

Here's a line taken from working code:

Please Login or Register  to view this content.

Where 89 is a sheet name.

[msmith1955]

In the Test documents this is what is written in the first column. I thought that the macro that I ran just used this to identify where the data would be taken from.
I have been trying the ActiveCell.FormulaR1C1 = "='89'!RC+1" using strFN in place of the 89
Sorry this still does not work. All I am writing to the cells are what is in the quotes.
If I put the entire path and file name where the 89 is I get #Value! in the cell. I am researching this further.
I am still working toward a resolution.
Thank You for the reply.

[msmith1955]

Sorry xladept After reading my first answer I do not feel I answered your questions improperly.
My lack of knowledge with VBA is also a contributing factor. Again I apologize. Books are on order.
I have researched this further and found that I have tied the column 2 cells of the Test documents to the names in the recorded macro. After the test the files will be written into the folder. I wish to be able to open the new file run the macro to add the files
to the combined data.
I have since deleted the cells containing the Named cells and am now using cell names (R?, C?).
After recording another macro and in this macro and making the cell = to the needed data I am getting the data as needed.
The problem is that I cannot open the file I want. This leads me to believe that my original issue is in the statements of the macro.
Original:

Please Login or Register  to view this content.

This is the new macro I recorded. (Unable to open files I need to open)
New MACRO:

Please Login or Register  to view this content.

This does work but I am unable to use files that I choose.
Thank you for your kind consideration.

[xladept]

Hi M,

Why don't you skip the Worksheet Formulas and just program what you want? BTW - exactly what is that??

[msmith1955]

Not too familiar with VBA. I want to open the test data excel. Each time I test the device it puts this data in a folder. I then want to put this data in a special customer spreadsheet. I need to learn more about how to accomplish this. I have been able with the code listed earlier to open a file of my choice and check the columns to find and empty file to write to. My issue is how to use any VBA special characters to copy the values. The code used earlier in this forum does not appear to be getting the values and passing them to the needed file. I will probably try to write the program my self. In the code that follows does this not make strFN equal to the filename?

Please Login or Register  to view this content.

Early next week I will try to write new code. Thank you for your assistance.

[xladept]

Hi M,

Actually strFN is the FullName - you may want just the ActiveWorkbook.Name???

[msmith1955]

Thank you. I will look at changing this.

[msmith1955]

I may have found an issue. Not sure how to fix it.
In the code listed below I thought the !R4C2 represented the file that was already open but it appears it does not this is tied to the original xlt file. When the device test is complete it creates a new .xls file I need for this code in particular the !R4C2 part to be tied to the open file would I need to activate this file or do something like this: !Worksheets(Test1).Cell(R4C2) ??????

Please Login or Register  to view this content.

[xladept]

Hi M,

Here's how I interpret your code:

Please Login or Register  to view this content.

Since you've a template - probably the correct coding is:

Please Login or Register  to view this content.

[msmith1955]

Thank you for the response. I believe the problem is that the work sheet TestIII is already open and I need to know how to set as in your example ws as this worksheet. The reason is that all the data needed is named differently but in the same folder.
Here is the process used Presently the data is taken from the test and written to an excel file from a template the template will hold the macro to write the data into another file, customer format. I see the code you have above takes the offset and makes it equal to the worksheet.sheet1.cells. How can I make the ws = to the open worksheet? This would be so I can open any data sheet in the folder. I am presently researching this.
Your code.

Please Login or Register  to view this content.

Thank you again.

[xladept]

At the top:

Please Login or Register  to view this content.

**I may be confused again?? - You are writing from a template??

BTW - If you open the template with the Right Click - Open it won't be a copy.

[msmith1955]

When the test runs it uses a template to fill out the information then it saves as a .xls file. I open this file to write data to customer spreadsheet. The macro I am writing is being tied to the template so that when the new file is created the send data button tied to the macro will open C:\ , then I choose the customers file and the macro checks for an empty cell in R17 Starting at CG. Then the data is
copied from the Test documents into the file. After I added the set ws=ActiveSheet I got a runtime error '424' object required. I am assuming that I am missing a reference. Following is my code. I have commented out some of my code at the bottom.

Please Login or Register  to view this content.

I have been busy with other projects and am just now looking at this program. Thank you for your assistance and kindness. I just received the book 'EXCEL 2003 Power Programming with VBA' by John Walkenback. I hope this will help me to have a better understanding.

[msmith1955]

I replaced:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

This may or may not be working code stops with the following code:

Please Login or Register  to view this content.

I know it is getting past the code to find an empty cell.
It appears to work but it does not write anything to the file.
I will look at this in depth tomorrow.

[xladept]

Hi M,

Take the quotes out of those cell references:

Please Login or Register  to view this content.

[msmith1955]

My code is working as expected thanks to xladept. Thank you xladept for your assistance and kind consideration.
Part of the issue was in my understanding of the Range and or Cells and how they use the offset.
This is my final code. Working Great.

Please Login or Register  to view this content.

[xladept]

Glad you got it and you're welcome!