Renaming new worksheet using date and name

**Dangermouse1977** · 03-11-2010, 12:47 AM

Hi Everyone,
I am having a issue writing a macro. See attached Excel file..I am trying to rename a worksheet with a date (Cell D1) and the first 5 letters of a name (CellB2) and then have the F2:F28 column transfer into another worksheet (Campbells!) based on the date (Cell A3:E3) Any help with this would be fantastic!!!!
Thanking you in advance
Matthew

**excelxx** · 03-11-2010, 05:45 AM

Hi Matthew

I think the code below should help you towards acheiving your desired result.

Code:

Sub rename()
    Dim wsMain As Worksheet, wsSum As Worksheet
    Dim rng As Range
    
    Set wsMain = Worksheets(1)         'dateneeded
    Set wsSum = Worksheets(2)         'Campbells
    wsMain.Name = Replace(wsMain.Range("D1"), "/", "") & " " & Left(wsMain.Range("B2"), 5)
    For Each rng In wsSum.Range("A3:E3")
        If wsMain.Range("D1") = rng.Value Then
            col = rng.Column
            Exit For
        End If
    Next rng
    For n = 1 To wsMain.UsedRange.Rows.Count
        wsSum.Cells(3 + n, col) = wsMain.Cells(1 + n, "F")
    Next n
    
End Sub

**Dangermouse1977** · 03-15-2010, 07:13 PM

Hi,
It renamed the new worksheet, but didnt transfer the data into the campbells worksheet....and wants to debug on the following line...
wsSum.Cells(3 + n, col) = wsMain.Cells(1 + n, "F")
???
PS I am using Office 2003 (Im not sure if that makes a difference for VB code?)

lol thanks for the quick reply though, that was awesome!
Matthew

**excelxx** · 03-16-2010, 06:37 AM

Hi Matthew

The problem is that the cell D1 has a formula '=TODAY()' as a result the date in the cell is not available on the other sheet.

I have added some additional code, to let you know that the date has not been found.

Code:

Sub rename()
    Dim wsMain As Worksheet, wsSum As Worksheet
    Dim rng As Range
    
    Set wsMain = Worksheets(1)
    Set wsSum = Worksheets(2)
    wsMain.Name = Replace(wsMain.Range("D1"), "/", "") & " " & Left(wsMain.Range("B2"), 5)
    For Each rng In wsSum.Range("A3:AE3")
        If wsMain.Range("D1") = rng.Value Then
            col = rng.Column
            Exit For
        End If
    Next rng
    If col = "" Then
        MsgBox "The date " & wsMain.Range("D1") & " cannot be found on the summary sheet"
    Else
        For n = 1 To wsMain.UsedRange.Rows.Count
            wsSum.Cells(3 + n, col) = wsMain.Cells(1 + n, "F")
        Next n
    End If
End Sub

Regards