newbie: VBA to count number of lines on another workbook, then copy data into there.

[charlie543]

Hi all,

im hoping somebody may be able to help me on this macro?

basically im wanting to double click on a cell, then copy that to the top row, the top row is linked by a function to the row below. (which it does)

the vba should then open the 2nd workbook (which it does)

the macro then finds an empty column (however it is counting for the first worksheet not the second)

it then pastes the data into place. (which is currently the wrong place)

Thanks for any help

Charlie

Code:

Please Login or Register  to view this content.

[dflak]

You have this set up as a double-click event and the way you have it set up is that if you double click anywhere on the page, the code will execute. Do you wish to limit what kicks off the execution to a limited range of cells?

You have this set up as a double-click event and the way you have it set up is that if you double click anywhere on the page, the code will execute. Do you wish to limit what kicks off the execution to a limited range of cells?

This looks like it was a recorded macro. In general whenever you see:

Please Login or Register  to view this content.

You can replace it with

Please Login or Register  to view this content.

In a short program like this it doesn’t matter but if you are looping though thousands of cells of data, select will slow you down.

When working with multiple workbooks, it’s usually a good idea to set a “pointer” to them. The same holds true for worksheets.

In this case I have a pointer called xlFile to the workbook, and sh2 to a specific sheet in the workbook. The nice thing about pointers to sheets in workbook is that the pointer remembers which workbook it’s in. So if you want to copy something from sh1 in one workbook to sh2 in another use:

Please Login or Register  to view this content.

It doesn't matter if sh1 and sh2 are in the same workbook or not.

If you are copying all (meaning values, formats, formulas, etc.) then the shortcut for that is

Please Login or Register  to view this content.

If you just want to copy values then use

Please Login or Register  to view this content.

If you copy an entire row and you want to paste to an entire row, you don’t have to select the entire row for the destination. Selecting or addressing the first cell on the row is good enough.

Here is the code – complete with comments. Do not have the second file so I could not test this code.

Please Login or Register  to view this content.

[charlie543]

Hey thanks for the help, however unfortunately it is not solved.

If I C&P the code in and run use it - it highlights:
LRow2 = sh2.Range("C" & Rows.Count).End(xlUp).Row

and says "method 'range' of 'object' _Worksheet' Failed

so I went back to using my row search function where the program would just put data into about row 2000 ish (I would hopefully like to change it to start the search at row 3000 also)

as a test I deleted most of the initial rows, and re ran it a few times. and it would replace say row 178, then 179, then 180... etc

Should I send you the file?

[dflak]

Sending the file will definitely help

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[charlie543]

I think ive attatched theExcel sheets here.

So just to be clear,

On the SIR worksheet, I want to double click on a row, then this data is copied to the top row, which is converted into the second row.

The second line should then be copied onto the second workbook (sample inspection report.....) on the next available row. and if possible it would be nice to make it return the associated number with that row - but I can probably figure that out myself.

Thanks for the help, macros are hard

[dflak]

Here is the workbook. It assumes that the Inspection report is in the same directory with it. If not, I've annotated where in the code this needs to be changed.

[dflak]

Here is the spreadsheet. It assumes that the Inspection Report is in the same directory with it. If not, I've indicated in he code what needs to be changed.

[charlie543]

Hey dflak thank you it does work really well, ill try use what you have taught me in future work. Thank you very much