Solved

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

Posted on 2013-06-24
7
452 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

749 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