Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

**N1ka** · 03-30-2018, 12:42 PM

Hi,

it didn't take me long...

I have one Workbook with some data populated in every row for one job.
I need to make an invoice for every pre-selected job (row).
The data from the every job row is used in the Macro-enabled file, and it is fulfilled OK when it is manually pasted to the first row in the destination file.
The file is then saved with the pre-defined name from a cell inside the copied row (linked to a cell "AL4").
The file is the printed as PDF, with the same name, just in a parent folder in regard to excel file.

I am trying to automate the process.
The code below is my first try, without the errors given. I have managed to correct all the errors reported (or at least I think so - it ein' reportin' nothin' anymore)

Now it don't gives me errors, but it also - don't give me anything. When I press the macro button - nothing happens.

A little help?

Thanks in advance,
Nika

Please Login or Register  to view this content.

**dflak** · 03-30-2018, 02:29 PM

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.

**N1ka** · 03-31-2018, 06:32 PM

Hi Dflak,

thank you for the answer.

Please find attached zip file with 3 files:

- T1 - The workbook containing base table, where the start data is entered. From here a row by row is copied, and entered in the invoice template, one at the time.

- YYYY-MM-DD RBR V (PR).XLSM is the macro enabled workbook, containing the invoice template. The copied row is pasted here in the first row. There are links in the invoice to different cells in the first row, that populates that cells.

- 2018-01-02 001 M ET - M AM (KB).XLSM - This is how the populated invoice look like.

- 2018-01-02 001 M ET - M AM (KB).PDF. This file is not uploaded, but this is just the PDF version of the invoice.

If you need any more info, I will be glad to provide it.

Thanks again for your time and knowledge,
Cheers,
Nika

**TMS** · 03-31-2018, 07:12 PM

This

Please Login or Register  to view this content.

would give an error because, as far as I can see, there is no label

Please Login or Register  to view this content.

I suspect the reason it does nothing is because you don't appear to initialise the variable NmbSelRows.

Not tested the files because these are basic errors that need to be fixed and tested before you move on.

Use F8 to step through the code to see what's happening. Hover the cursor over variables to see what values they have.

**N1ka** · 04-01-2018, 03:43 PM

Hi,

Happy Eastern to all of you who celebrate by the Gregorian calender.

When I press the macro button placed on the worksheet - nothing happens.
However, if I select the "Sub OpenRacExcel()" and press the play button, it opens the "YYYY-MM-DD RBR V (PR).xlsm", as it is supposed to do!
So far, at least a part of the macro is working.

I have tried to step trough the code with F8, and it marks with yellow the command lines (except DIM commands):

Please Login or Register  to view this content.

Hovering over it

Please Login or Register  to view this content.

Hovering over it gives "Application.ScreenUpdating = True"

Please Login or Register  to view this content.

Hovering over it gives "mainworkBook = Nothing"

Please Login or Register  to view this content.

Hovering over it gives "PreSelRows = Nothing"

When it comes to the command line:

Please Login or Register  to view this content.

Hovering over it gives "ActCopyRow = 0"

it skips a lot of code, and the next yellow marked command is at the end:

Please Login or Register  to view this content.

Nothing happens, no error message is issued. I am officially confused.

Maybe this is the issue:
(I am not allowed to post the link, but there is a blog with the title "Excel VBA Problem With Step Into F8" that says:

"After you record or write a macro in Excel, you can run the macro, or go slowly through it, line by line, to see if it is working correctly. I use the F8 key, ...
However, for the past few months, something was going wrong with the “Step Into” command in Excel 2010, whether I used the menu, or the F8 key.
Code Doesn’t Stop...

In some macros, especially if the code opened another Excel file, when I pressed the F8 key, the code ran to the end of the macro, instead of stopping at the next line of code. That made it very difficult to troubleshoot the code!
It could also cause other inconvenient or embarrassing problems, if the macro was designed to update files, or send out emails. You don’t want that kind of thing happening at the wrong time. "
The solution is offered:
"Finally a Solution Found

Finally, my friend, Ross Connell, found a solution last week, and kindly shared it with me. Ross spent a long time communicating with Microsoft’s tech support, about this and a couple of other issues, and someone there suggested this fix.

The suggestion is not documented on the Microsoft website though, so don’t take this as an official recommendation from them. However, making this change fixed the problem for me, and for Ross. If you’re comfortable in making a change to your Windows Registry, you can try it too, at your own risk."

But I am not sure I want to mess with the registry, especially as I don't know what I am doing with this macro code as well.

Any help?

Thanks in advance,
Nika

**TMS** · 04-01-2018, 06:20 PM

Comment out this line:

Please Login or Register  to view this content.

Run the code again and see where it breaks. THEN look at what the variables contain.

**TMS** · 04-01-2018, 06:22 PM

You should look at the contents of variables AFTER the line of code has been executed.

Probably nothing wrong with using F8

**N1ka** · 04-02-2018, 09:50 AM

Hi TMS,

thanks for your time.

The "On Error Resume Next" is not commented anymore, but I have exact the same scenario as mentioned in my post (#5) - it just goes trough the code, stops at the mentioned lines, hovering above the variables after the code was run still gives the same result - nothing (that I am aware of) changes...
Something is fishy from the beginning - even though 2-3 rows are pre-selected in main workbook before I start the code, hovering over

Please Login or Register  to view this content.

gives "PreSelRows = Nothing".
Even Hovering over

Please Login or Register  to view this content.

gives "Application.ScreenUpdating = True" - does this means that even that basic code is not performed? And why don't I get the error?

Regards

**TMS** · 04-02-2018, 01:39 PM

ScreenUpdating = False is not effective when stepping through code ... otherwise you wouldn't see what is happening.

Please Login or Register  to view this content.

**N1ka** · 04-03-2018, 02:58 AM

Ok, there is some kind of a progress. Not in making the code, but in trying to figure out what's wrong, though.
Nevertheless - a progress is a progress...

Something is definitely wrong with the code:

Please Login or Register  to view this content.

After the code execution, it still shows "Memrow = Nothing"
After Querying "? MemRow" I get Run-time error '91' - Object variable or With block variable not set.

And it runs to the end of the code as before.

But, when I set :

Please Login or Register  to view this content.

debug actually goes trough all the code, it calls the sub and opens the new file, but it apears that nothing is copied so nothing is pasted to the first row, and when it attempts to save the file with the name of AL4 cell, it can't, because it is empty.

So, the conclusion: I should figure out how to code:
- put selected rows in memory
- count the numbers of selected rows
- for each selected row, copy it's content (and later after the new file is opened, paste it to the first row)

I will take a peak at the internet, but any help is of course appreciated.

Thanks in advance

**N1ka** · 04-03-2018, 04:55 AM

... Forgot to confirm:

Please Login or Register  to view this content.

makes no difference in execution to:

Please Login or Register  to view this content.

**N1ka** · 04-03-2018, 05:36 AM

It looks like I've managed to overpass the selected row counting problem. with:

Please Login or Register  to view this content.

I have sellected the range from 3.rd to 7.th row, and after execution, "ActCopyRow" shows "3" (and after second iteration, it shows "4".)

Let me assume that the counting rows problem is solved. Now to deal with copying the content of the each row, and pasting it to the first row of the destination file...

**N1ka** · 04-03-2018, 06:02 AM

... As I don't really know what I am doing, and I don't have a "licence to kill", I am firing in the dark, and learning by elimination process.
So - I have changed the code a bit, so the SUB that calls the Invoice is now incorporated in the main code because while experimenting, it went back to not executing the SUB, but flying to the end of the code )

.

By executing the code with F8, I have surpassed yet anther step:
It opens the Invoice workbook, and acctually I see that the "A1" cell is sellected, where the preselected row from the main WorkBook should be pasted, but it is not.

Please Login or Register  to view this content.

**N1ka** · 04-03-2018, 06:22 AM

When the code is supposed to "paste" the data to the "A1" row of the invoice WorkBook, I get the pop-up:
"Data on the clipboard is not the same size and shape as the selected area. Do you want to paste the data anyway?"
And no mater what I choose - "Ok" or "Cancel" - nothing is pasted.

So, let me assume that my first assumption was correct. I am not very good with copy/paste thingy...

My main suspect is

Please Login or Register  to view this content.

Yet to prove it guilty.

**N1ka** · 04-03-2018, 07:10 PM

Originally Posted by N1ka

... Forgot to confirm:

Please Login or Register  to view this content.

makes no difference in execution to:

Please Login or Register  to view this content.

That was until I have starting to change the code.
During the investigation, I definitely found out that copying of the selected rows and pasting one by one was not working. So, in one iteration, I changed the code, so I was able to copy and paste the rows. The tiny problem was - all the selected rows was pasted in the Invoice WB.

I had to force-select just one row. I pre-selected only 1 row (and had to change

Please Login or Register  to view this content.

And this did the trick. I was able to copy that one selected row, paste it to newly opened Invoice WB (after some more code changing), save it with the name taken from the cell "AL4"...
I had to make some more changes (don't ask me why - it wasn't working, I was experimenting a bit), and now it print to PDF also.

So - it looks like I have just one more thing to correct: Copy / paste row by row from pre-selected rows...

**N1ka** · 04-03-2018, 08:07 PM

Thank you all for helping me. I don't think I'd manage it with out your help.

I'll try to clean the code a bit and make it decent, then post it.

Cheers.

Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

LinkBack

Thread Tools

Rate This Thread

Display

Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Re: Export Pre-selected Rows to Macro-enabed File, Save the file, Print it as PDF.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Save selected print area as a pdf file using the text from a specific cells as the file na

Export Cells to Text and automatically have File Name and File Type Selected

Extract rows from one file that matches rows in a reference file and finally export result

how do I choose what folder .ps files are saved to when I go to File>Print>Save To File?

Copy Save File As then Print macro

Macro to Sort automatically when file/save is selected

[SOLVED] Macro to export a worksheet and save as new file

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions