Solved

excel macro to copy a set range to different worksheets in another workbook and rename the worksheets

Posted on 2013-06-24
7
447 Views
Last Modified: 2013-06-26
Hello there,

I have a set range in workbook A, Worksheet A, Range("A1", "C6").

Whenever there is a change in the range, I would like to copy the new range into workbook B as a new worksheet and then rename the sheet with the cells A2&C4 of the original range.

Example: Range1, cellA2= "10", cellsC4 ="test"

The macro is run and the SheetA.Range("A1", "C6"). Select.Copy
in Workbook B, a new sheet is created with the name 10 test, and the range is pasted into it. (values and formats)

The next time the original range is updated, the cells are now A2= 12 , Cells C4 =420
The macro is run with SheetA.Range("A1", "C6"). Select.Copy
in Workbook B, a new sheet is created with the name 12 420, and the range is pasted into it

So whenever the macro is run, a new worksheet is created in workbook B .

The macro should be run manually, not whenever the range is changed.

Thanks,
CC
0
Comment
Question by:CC10
  • 4
  • 3
7 Comments
 
LVL 5

Expert Comment

by:DOSLover
ID: 39271774
Create a macro in "workbook A" with the following code. Change the name of 'YourWorkBookB.xlsx" to your second workbook. It uses a variable called newName to rename the new worksheet created in YourWorkBookB.xlsx.  
Sub CopyCellsAndRenameSheet()

Dim newName
    Range("A1:C6").Select
    Selection.Copy
    Windows("YourWorkBookB.xlsx").Activate
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    newName = Range("A2").Value + Range("C4").Value
    ActiveSheet.Name = newName
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

Open in new window

0
 

Author Comment

by:CC10
ID: 39272607
I have some problems.

This did not work:
Windows.("C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\TEST\test.xlsx").Activate      

So I tried this and it still did not work:
Workbooks.("C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\TEST\test.xlsx").Activate

N.B. the file "test.xlsx" was open at the time

Then I tried this and it did open the file:
Workbooks.Open Filename:="C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\TEST\test.xlsx"


But now it blocks at Active Sheet.paste
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39273287
I had the macro working when both workbooks are open. If the Workbook-B is not open, you will need to change the code as follows. Please change file name if necessary. Also please note that the macro closes Workbook-B ("TEST.xlsx") at the end.
    Dim newName
    Range("A1:C6").Select
    Selection.Copy
    Workbooks.Open Filename:="C:\Users\Centa\Dropbox\Centa\Centa FXDP\Trader Journals\TEST\test.xlsx"
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    newName = Range("A2").Value + Range("C4").Value
    ActiveSheet.Name = newName
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close

Open in new window

0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:CC10
ID: 39274548
That works. Thanks.

One change I would ask for.

in the code below, the name adds the two cell amounts.
newName = Range("A2").Value + Range("C4").Value

So, if A2=120 and C4=20, the the Sheet name will be 140.

I would prefer them not to add but to appear as follows:

New name:  "120 and 20"

Can you change this please?

Thanks,
CC
0
 
LVL 5

Accepted Solution

by:
DOSLover earned 400 total points
ID: 39274939
Please replace this line
newName = Range("A2").Value + Range("C4").Value
With this one
newName = Range("A2").Value & Range("C4").Value
0
 

Author Comment

by:CC10
ID: 39274987
OK this all works now but I have used now used the proper range and I get this error box:

A formula or sheet you want to move or copy contains the name 'Order' , which already exists on the destination worksheet. do you want to use this version of the name?
To use the name as defined in the destination sheet, click Yes
to rename the range referred to in the formula or worksheet , click No, and enter a new name in the Name conflict dialog box

If I click "Yes, the macro continues correctly and we are finished.

Is there a way to override the error box?

Sorry about this last hitch.

Thanks,
CC
0
 
LVL 5

Expert Comment

by:DOSLover
ID: 39278494
I couldn't get the same error. I am just trying to see the steps you did. You opened the first workbook-A, made changes. But values in A2 and C4 didn't change. Ran the macro. It tries to create a new sheet, with name A2+C4, and throws error (because sheetname already exists), right?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associateā€¦
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns overā€¦
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question