Solved

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

Posted on 2013-06-24
7
418 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:CC10
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now