Rename New Workbook Based on Condition from other Sheet

R4yHunt

New Member
Joined
Aug 11, 2018
Messages
3
Hi, i already code some script for renaming the new workbook. But i have trouble to rename it based on condition. Here's the code that i scripted. I've been using this code for a long time. But now i need to put additional condition. Here's what i'm looking for:
1. Insert an additional name in front of the new workbook created by this script by using conditional method. The additional name can be found in reference sheet.
2. 1st sheet Column A contain lots of figures and reference sheet contain the id of 1st sheet column A and the column b (ref sheet) contain the name. I need the macro to search the 1st sheet id in ref sheet and insert the additional name of the workbook from ref sheet column b. Kindly advise.

Code:
sub split()

extn = inputbox(prompt:="input the extension for file", title:"extension",default:="")
tempname=activeworkbook.name
patt=thisworkbook.path


i=1
do while cells(i,1)<>"base"
i=i+1
loop
i=i+1
do while cells(i,2)<>""
abc=cells(i,2)
a=i
do while cells(i,2)=abc
i=i+1
loop


range(a&":"&i-1).select
selection.copy
set tempWB=workbooks.add(1)
tname=activeworkbook.name
cells(3,1).select
selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false
selection.pastespecial paste:=xlpasteformats, operation:=xlnone, skipblanks:=false, transpose:=false


workbooks(tempname).activate
rows(2).select
selection.copy
workbooks(tname).activate
cells(2,1).select
selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks:=false, transpose:=false
selection.pastespecial paste:=xlpasteformats, operation:=xlnone, skipblanks:=false, transpose:=false
cells.entirecolumn.autofit
columns("b:B").select
selection.columnwidth=12.14
'	Workbook.name=abc
activeworkbook.saveas patt & "\" & abc & "_" & extn
activeworkbook.close
Loop
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top