PDA

View Full Version : Solved: Flippin awkward task!



Sir Babydum GBE
11-07-2005, 08:12 AM
Hi

I need some help with a question similar to this one http://www.vbaexpress.com/forum/showthread.php?t=5837

On a sheet called "sequenced modules", Column B contains strings that are to become the sheet names. Columns C to EB contain text that i want to export to the new sheet.

For example:

If B2 says "Babydum", C2 says "Gorgeous" and D2 says "Sexy" I would like the macro to create a sheet within the same workbook named "babydum", and in cells C2 and D2 of that sheet, it should say ""Gorgeous" and "Sexy" respectively. Then The macro will look at line 3 and repeat the process.

It needs to do this upto and including line 119, and as i mentioned, there is actually text in loads of columns - up to EB - which i need to put into the new sheets.

Killian
11-07-2005, 08:50 AM
Hiya,
Couple of questions...
Are they all going in the active workbook "Badydum", or might that change requiring you to open the appropriate workbook? Do you need to check if the workshhet already exists?

Sir Babydum GBE
11-07-2005, 08:59 AM
Hi Killian

Sort of... There'll just be the one sheet, so i don't need to check for duplicates. In my example, "Babydum" is the name of a sheet, not the workbook. It is created because the word "babydum" appears in column B, row 2 (for example) of the originator sheet. Then once a new sheet has been created (yes - within the same workbook) it will go to line 3 of the originator sheet and create a new sheet which will be named after whatever appears in B3. Then it will do the same for line 4, and so on.

So the finished product will be a very big workbook containing over a hundred sheets.

Thanks for your help with this Killian :thumb

mvidas
11-07-2005, 09:15 AM
Hey BD,

I assumed that when you add whatever sheet is in row 3, you still want the data copied to row 2 of the new sheet instead of 3 (so when you add the sheet in B99 you're not copying the data to row 99 on the new sheet, etc).

Try the following, should do what you need:Sub FlippinAwkwardTask()
Dim WS As Worksheet, NewWS As Worksheet
Dim NewWSNameCol As Range, DataToCopyCols As Range, NewName As Range
Dim RowToCopyTo As Long

Set WS = Sheets("sequenced modules") 'worksheet with source data
Set NewWSNameCol = WS.Columns("B") 'column with new sheet names
Set DataToCopyCols = WS.Columns("C:EB") 'column with data
RowToCopyTo = 2 'row to copy to on generated worksheet

Set NewWSNameCol = Intersect(NewWSNameCol, WS.UsedRange, WS.Rows("2:65536"))
If NewWSNameCol Is Nothing Then Exit Sub 'no source data
Application.ScreenUpdating = False
For Each NewName In NewWSNameCol.Cells
On Error Resume Next
Set NewWS = Sheets(NewName.Text)
On Error GoTo 0
If NewWS Is Nothing Then
Set NewWS = Sheets.Add(After:=Sheets(Sheets.Count))
NewWS.Name = NewName.Text
With Intersect(NewName.EntireRow, DataToCopyCols)
Intersect(NewWS.Range(.Address).EntireColumn, NewWS.Rows(RowToCopyTo)).Value = .Value
' .Copy Intersect(NewWS.Range(.Address).EntireColumn, NewWS.Rows(RowToCopyTo))
End With
Else
MsgBox "Sheet '" & NewName.Text & "' already exists. Skipping this sheet."
End If
Set NewWS = Nothing
Next
Application.ScreenUpdating = True
End SubMatt

Killian
11-07-2005, 09:16 AM
OK, I think I get it (?)Dim c As Range
Dim shtNew As Worksheet

Application.ScreenUpdating = False

With ThisWorkbook.Sheets("sequenced modules")
For Each c In .Range("B2:B119") ' for each cell in col b (rows 2 to 119)
'add a new sheet
Set shtNew = ThisWorkbook.Sheets.Add
'name the new sheet with text of that cell
shtNew.Name = c.Text
'copy the range from col C to EB to the new sheet starting in B2
.Range(c.Offset(0, 1), c.Offset(0, 130)).Copy shtNew.Cells(2, 2)
Next
End With

Sir Babydum GBE
11-07-2005, 09:51 AM
Thanks both - I appreciate your time. So much so that I grant you both two weeks worth of everlasting life, absolutely free.

Works a treat!