Copy from template to first empty row in a master workbook?

**dylanemcgregor** · 12-30-2009, 05:38 PM

Hello all,

I apologize in advance for what seems to be a common theme in questions, but although I've found similar questions the details seem to always be different enough that I'm unsure how to adapt the code to suit my needs.

I have an Excel workbook that is created a few times a month from a template. When I update it I run a macro that saves a values only copy with the current date. I'd like to copy non-blank rows (from row 8 down) into the first empty row in a master workbook that is saved on a network drive. The path to the master workbook is "B:\8MP\BG\Trade Pre-Advice\BG Trade Confirm Master.xls"

I'd appreciate if someone can get me started in the right direction.

Thanks,
Dylan

**Leith Ross** · 12-30-2009, 07:56 PM

Hello Dylan,

This macro will copy the data from the data from the active workbook to the master workbook. The worksheets in both workbooks have been initialized to "Sheet1". You may need to change the sheet names.

Please Login or Register  to view this content.

**dylanemcgregor** · 12-30-2009, 08:46 PM

Hi Leith, thank you very much for taking the time to post this. When I try and run I get a "runtime error '9' subscribt out of range" and when I try and debug it highlights the Set DstWks line. I have double checked that the path is correct and I have updated the worksheet name to "BG trades" Any ideas what the problem might be?

**Leith Ross** · 12-30-2009, 08:50 PM

Hello Dylan,

Is the worksheet name correct? I used "Sheet1" in the code.

**dylanemcgregor** · 12-30-2009, 08:57 PM

Hi Leith. I updated the DstWks worksheet from "Sheet1" to "BG trades" the SrcWks I left as "Sheet1" as that is the name in my source worksheet. Those are the only two places where I would need to be concerned about the explicit worksheet names, correct?

**Leith Ross** · 12-30-2009, 10:49 PM

Hello Dylan,

Sorry for suxh long delay in responding. The furnace is being replaced and I had to help the installer. It gets dark before 5:30 here. He was cutting some wood to frame the crawl space entrance.

To answer your question, yes those are the only variables you need to change. I ran the macro with 2 workbooks and had no problems. If you are still getting the "subscript out of range" error, post the workbook so I can review it.

**dylanemcgregor** · 12-31-2009, 04:42 PM

Hi Leith

I appreciate all the help you have given, and certainly no need to apologize for a delay in giving free advice! I would have responded sooner as well, but the forum website seemed to be down for most of the day for me.

The path I gave you for the Master Workbook is slightly different than the actual path as I abrv. the names of the client that were in the path to make it more anonymous, but I did change to the correct workbook path in the code you gave.

I've uploaded the template file I'm using. I've currently set-up your code as a separate macro in module 2. Assuming I get everything to work right with this code I would want to combine these macros eventually, but that can come later.

Once again appreciate any help you are able to give, but hey it is the holidays and I want you to get your furnace fixed (hopefully it isn't as cold in SF as it has been in NYC the last few days), so please don't feel any rush to respond. Best wishes for a happy and prosperous New Year.

Dylan

**Leith Ross** · 12-31-2009, 05:14 PM

Hello Dylan,

The weather here is never as cold as NYC, but when it hits in the 30's having no heat is not good. They did get it running. The new furnaces have built-in computers to run diagnostic checks before they will turn on. A string gust of wind blowing over the flue can prevent the furnace from firing up. The installer said it makes 35 checks before it ignites the gas.

So, the problem is still with the destination workbook? I will setup a second workbook for testing. Is there anything special I need to know about?

**dylanemcgregor** · 01-01-2010, 03:19 PM

Yeah, the problem still seems to be with the destination workbook. Is there something I am missing before I run the code? Should the destination workbook be open or closed when I run the macro in the source workbook? I'm trying to think of anything else I might be missing, but nothing is coming to me at least at this step.

**Leith Ross** · 01-02-2010, 10:43 PM

Hello Dylan,

The workbooks need to be open. If you attempt to reference a workbook that isn't open, you will get a "Subscript out of range" error.

**dylanemcgregor** · 01-04-2010, 10:25 PM

Hi Leith,

I have tried with the master workbook open and I am getting the same error and the same line in the code highlighted. Any other ideas?

Thanks,
Dylan

**Leith Ross** · 01-04-2010, 10:37 PM

Hello Dylan,

Would it possible to post the master and one of the other workbooks you are using?

**dylanemcgregor** · 01-05-2010, 07:50 PM

Hi Leith. I apologize for the delay between messages. I have conferences that I'm attending all week, so I only get a few minutes in front of the computer each day. The workbook posted above is one of the source workbooks. On this message I'm attaching the master, but I have deleted out all of the content and just left column headers.

Thanks again for all of your time. I won't be online again until tomorrow night, so that will be the first time I can take a look at anything.

Dylan

**Leith Ross** · 01-05-2010, 10:30 PM

Hello Dylan,

Thanks for the blank master. It seems you are referencing a Add-In. Whenever the macro runs and tries to update the master, a dialog appears (see attached jpg). This tells me there is more going on in the code than what you have told me about. What is Blp.xla and what is it doing?

**dylanemcgregor** · 01-06-2010, 02:28 PM

blp.xla is a Bloomberg add-in that lets Excel pull in information from Bloomberg. I think it is present in all workbooks that I create on this workstation whether or not I'm actively using it. I do use the addin in the template worksheet to autofill some ofthe other columns (I type in the ticker and the name and ISIN number for the company are pulled in from Bloomberg). I am not actively using this in the master workbook.

Any ideas on how to proceed?

Copy from template to first empty row in a master workbook?

LinkBack

Thread Tools

Rate This Thread

Display

Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Re: Copy from template to first empty row in a master workbook?

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions