Convert A1 value to a sheet name and copy to it

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

I bit off more than I can chew trying to offer a solution in another forum and no one seems to respond there.

Sheet1 cell A1 = 1/7/2013 (mmddyyyy)
How can I convert that value to "January 2013" and insert it in the copy code below to make:

Option Explicit
Sub AtoCPaste()
Range("A1,A3,A5").Copy
Sheets("Sheet3").Range("C1000").End(xlUp).Offset(1, 0).PasteSpecial
End Sub

To read:

Option Explicit
Sub AtoCPaste()
Range("A1,A3,A5").Copy
Sheets("January 2013").Range("C1000").End(xlUp).Offset(1, 0).PasteSpecial
End Sub

Other A1 entries may be different months in 2013.
Sheets named January 2013 thru December 2013 already exist in the workbook.

Thanks,
Howard
 
Hi Howard,

Am Sat, 12 Jan 2013 00:48:20 -0800 (PST) schrieb Howard:
Option Explicit
Sub AtoCPaste()
Range("A1,A3,A5").Copy
Sheets("January 2013").Range("C1000").End(xlUp).Offset(1, 0).PasteSpecial
End Sub
Click to expand...

try:
Sub AtoCPaste()
Dim shName As String

With Sheets("Sheet1")
shName = Format(.Range("A1"), "MMMM YYYY")
.Range("A1,A3,A5").Copy
Sheets(shName).Range("C1000").End(xlUp).Offset(1, 0).PasteSpecial
End With
End Sub

Regards
Claus Busch
 
Hi Howard,



Am Sat, 12 Jan 2013 00:48:20 -0800 (PST) schrieb Howard:








try:

Sub AtoCPaste()

Dim shName As String



With Sheets("Sheet1")

shName = Format(.Range("A1"), "MMMM YYYY")

.Range("A1,A3,A5").Copy

Sheets(shName).Range("C1000").End(xlUp).Offset(1, 0).PasteSpecial

End With

End Sub



Regards

Claus Busch

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...

Wow, what could be better than that? And when I see the solution it seems so simple.

I will include your name in credit for what appears to be "hands tied behind your back and blindfolded easy" for you when I post it in the other forum.
(And to my archives)

Thanks again.

Regards,
Howard
 
Back
Top