Solved

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

Posted on 2013-06-24
7
440 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

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.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

831 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