Worksheet and cell value matched to range

praveen23

Board Regular
Joined
Jun 24, 2004
Messages
121
Hi all,

I have a worksheet with cell values A4-30.

I want my VBA script to create a new worksheet for every one of these values that is titled after the value, then copies a range of A1:G60 from a worksheet called Default, and that puts the value also into cell B2 of each new worksheet. I want these new sheets to appear on the right (towards the end of the workbook).

Right now I am trying the two modules below. The worksheet titling is working fine but new sheets are being created on the left, and the B2 formula (=TRIM(MID(@CELL("filename"),FIND("]",@CELL("filename",A1))+1,255))) doesnt' appear to be updating as the cells are copied.

'Name macro
Sub CreateSheets()

'Dimension variables and declare data types
Dim rng As Range
Dim cell As Range

'Enable error handling
On Error GoTo Errorhandling

'Show inputbox to user and prompt for a cell range
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)

'Iterate through cells in selected cell range
For Each cell In rng

'Check if cell is not empty
If cell <> "" Then

'Insert worksheet and name the worksheet based on cell value
Sheets.Add.Name = cell
End If

'Continue with next cell in cell range
Next cell

'Go here if an error occurs
Errorhandling:

'Stop macro

End Sub


Sub CopyData()

Worksheets("Default").Range("A1:J60").Copy

ActiveSheet.Range("A1:J60").PasteSpecial Paste:=xlPasteFormulas
ActiveSheet.Range("A1:J60").PasteSpecial xlPasteColumnWidths
ActiveSheet.Range("A1:J60").PasteSpecial Paste:=xlPasteFormats

End Sub
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
To add the sheets to the left, you could try:

VBA Code:
'Insert worksheet and name the worksheet based on cell value
Sheets.Add(After:=Sheets(Sheets.Count)).Name = cell

To copy the range to B2, you could try

Code:
Worksheets("Default").Range("A1:J60").Copy ActiveSheet.Range("A1:A10").Offset(1, 0)

I would also make sure to call the copyData procedure in the CreateSheets procedure so excel does not get confused which sheet is the active sheet.
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

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