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.

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, 06: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, 05: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.

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