• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

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

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
CC10
Asked:
CC10
  • 4
  • 3
1 Solution
 
DOSLoverCommented:
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
 
CC10Author Commented:
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
 
DOSLoverCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CC10Author Commented:
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
 
DOSLoverCommented:
Please replace this line
newName = Range("A2").Value + Range("C4").Value
With this one
newName = Range("A2").Value & Range("C4").Value
0
 
CC10Author Commented:
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
 
DOSLoverCommented:
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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now