PDA

View Full Version : Copy cell range to anther sheet containing;..



menor59
03-13-2013, 03:43 PM
Hello all and thank you for viewing...

How would i copy from a worksheet called Blank range AE11:AR11

and Paste it to a sheet that called and contains... Union dues for * in G4:T4

The reason why for the Wildcard is because after that its a date that changes on the last day of the year...

ie...

Union dues for Mar 31, 2013
another sheet could be Apr 31, 2013 etc etc hense the wild card

then on that Union dues for * sheet...Adjust the column so you can read everything rather than it being cut off...you know move your mouse to the Upper left square to select all Cells then double click the line between the columns.

SamT
03-13-2013, 04:28 PM
The reason why for the Wildcard is because after that its a date that changes on the last day of the year...
Last day of year?

menor59
03-13-2013, 04:30 PM
err of the month..

I just need to get that range on the Blank Sheet AE11:AR11 to the sheet called 'Union dues for *' on range G4:T4

sorry typo

SamT
03-13-2013, 04:52 PM
Menor,

You are doing pretty good on understanding VBA. A real good way to learn more, faster is to record macros while doing what you want done, then studying the macro.

This also gives us a starting point to help you perfect the macro into a Procedure. The difference is that a macro can only do the same thing over and ever, but a Procedure can do different things depending on circumstances.

Before you record this macro, practice copying the range and pasting it into it's place and setting the column widths.

Start over from scratch and this time record a macro. choose to save the macro in "This Workbook," then after you record it, open the VBA editor and you will find it in Module1.

Study it to see what it is doing, then post it here and we will train you how to clean up and upgrade a macro to a procedure.

SamT
03-13-2013, 05:38 PM
Saving this here for future use.
Option Explicit

Sub test()
Dim X
X = Format(dhLastDayInMonth, "mmm dd, yyyy")
End Sub

Function dhLastDayInMonth(Optional dtmDate As Date = 0) As Date
'http://msdn.microsoft.com/en-us/library/aa227522%28v=vs.60%29.aspx
' Return the last day in the specified month.
If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
'SamT: Add a month to Date, return zeroth date of new month (LDoM)
dhLastDayInMonth = DateSerial(Year(dtmDate), _
Month(dtmDate) + 1, 0)
End Function

SamT
03-13-2013, 05:46 PM
Just for Menor:
Sub test()
Dim X
X = NewShtNameByLastDay
End Sub

Function NewShtNameByLastDay() As String
NewShtNameByLastDay = Format(DateSerial(Year(Date), _
Month(Date) + 1, 0), "mmm dd, yyyy")
End Function