Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    copy data from one area to another (windows xp)

    I am trying to copy a blocks of information from one section of a sheet to another on a weekly basis. I have a form that I put information in running from A2 to N 20. The information is strictly numeric. I then copy the entire block to AA2 to AN20 for the first week. Then the second week I would copy the information to AO2to BB20 and this would continue for the month. Some weeks have 4 weeks and some have 5 so how far over the sheet goes pasting the information, which started at AA2, depends on the number of weeks in the month. Once the month is complete I would drop down to the AA21 and start the process all over again for the next month and so on. Is there a macro that can be constructed to do this same thing? I want to make sure that it does not over right the previous information from the week before, so is there a way to pause the macro to allow the user to define where the start of the information should be? I also need this macro to be able to do this as many times as I desire.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: copy data from one area to another (windows xp

    Are there always 18 rows in the data (ie. so data for month 1 would begin at A2, month 2 at A21, month 3 at A40, month 4 at A59, etc.)?
    Waggers
    If at first you do succeed, you've probably missed something.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy data from one area to another (windows xp)

    Dave,
    As of this moment the answer is yes. It could go larger or smaller in the future. If you have a code that will work for the size as it stands I can edit the cell parameters later if necessary.
    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy data from one area to another (windows xp)

    How do you determine to which month a week belongs? For example: a week belongs to the month of its first day, or: a week belongs to the month containing 4 or more of its days.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy data from one area to another (windows xp)

    Hans,
    Good question with the information at hand. My key here is not "month" as much as number of weeks. The system is geared on a 4,4,5 accounting principle. So, put another way instead of "MONTHS' being the factor it is actually full weeks being combined to create the 4,4,5 system. and they would go from Sunday to Saturday. I hope this clears this and doesn't make it even muddier. ! [img]/forums/images/smilies/smile.gif[/img]
    Thanks

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy data from one area to another (windows xp)

    Try this. CopyData is an auxiliary procedure, CopyDataInput is the macro to run, and Test is a macro to test CopyDate (on a copy of the workbook!)

    Sub CopyData(datDate As Date)
    Const lngHeight As Long = 19
    Const lngWidth As Long = 14
    Dim intWeek As Integer
    Dim intWeek13 As Integer
    Dim lngRow As Long
    Dim lngCol As Long
    intWeek = CInt(Format(datDate, "ww"))
    intWeek13 = (intWeek - 1) Mod 13
    lngRow = (intWeek * 3 13 + (intWeek13 = 12)) * lngHeight + 2
    lngCol = (intWeek13 Mod 4 - 4 * (intWeek13 = 12)) * lngWidth + 27
    Range("A2").Resize(lngHeight, lngWidth).Copy Cells(lngRow, lngCol)
    End Sub

    Sub CopyDataInput()
    Dim strDate As String
    Dim datDate As Date
    strDate = InputBox(Prompt:="For which date?", Default:=Date)
    If IsDate(strDate) = False Then
    Beep
    Exit Sub
    End If
    datDate = CDate(strDate)
    CopyData datDate
    End Sub

    Sub Test()
    Dim datDate As Date
    For datDate = #1/1/2005# To #1/1/2006# Step 7
    CopyData datDate
    Next datDate
    End Sub

  7. #7
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy data from one area to another (windows xp)

    HANS,
    Thank you, I will try and reply.

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy data from one area to another (windows xp)

    Hans,
    I entered the code into a spreadsheet and tried to run the macro and got this as a reponse
    Const lngHeight As Long = 19
    Const lngWidth As Long = 14
    Dim intWeek As Integer
    Dim intWeek13 As Integer
    Dim lngRow As Long
    Dim lngCol As Long
    intWeek = CInt(Format(datDate, "ww"))------- The macro stopped at this line and the error message read "type mismatch"
    intWeek13 = (intWeek - 1) Mod 13
    lngRow = (intWeek * 3 13 + (intWeek13 = 12)) * lngHeight + 2
    lngCol = (intWeek13 Mod 4 - 4 * (intWeek13 = 12)) * lngWidth + 27
    Range("A2").Resize(lngHeight, lngWidth).Copy Cells(lngRow, lngCol)
    End Sub

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy data from one area to another (windows xp)

    When the macro halts, hover the mouse pointer over datDate. What value is displayed?

  10. #10
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy data from one area to another (windows xp)

    Hans,
    First let me say I do appreciated all the help. The information that is displayed when I hover over the area is: "datDate=Null" One other request, as a learn for myself and other novices that read this ,could you explain the macro line by line. It would be a great help in expanding the old grey matter.
    Thanks
    cvbs

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy data from one area to another (windows xp)

    It is strange that datDate is Null; this shouldn't happen. Perhaps you call the macro incorrectly, or else you made a mistake while copying the code. Please check very carefully.

    (I may post an explanation later)

  12. #12
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy data from one area to another (windows xp)

    Hans,
    I just created a small macro using the recording process in Excel. Made a shortcut ctrl k to start the macro. Once that was complete I went back into the macro and edited out the "recorded" code and pasted in yours. Thanks for the possible on the explanation post later also.
    Thanks,
    cvbs

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy data from one area to another (windows xp)

    Here is a test workbook with some dummy data, the macros and three buttons.
    The first button prompts you to enter a date, then copies A2:N20 to the range for that week.
    The second button copies A2:N20 for every week of the year, just to test if the 4 - 4 - 5 scheme works correctly.
    The third button clears all copied data, so that you can start over.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: copy data from one area to another (windows xp)

    Here is the code, with comments

    This procedure takes a date as input. The programmer must check that the input is valid before calling the procedure, it doesn't do any error checking itself.
    Sub CopyData(datDate As Date)

    The constants define the number of columns and rows that will be copied. Can be changed as needed:
    Const lngHeight As Long = 19
    Const lngWidth As Long = 14

    Some variables used in the macro:
    Dim intWeek As Integer
    Dim intWeek13 As Integer
    Dim lngRow As Long
    Dim lngCol As Long

    intWeek is the week number (1 ... 53)
    intWeek = CInt(Format(datDate, "ww"))

    Since the year is divided into 13 week periods (4+4+5), we calculate the remainder of the week number (after subtracting 1) when divided by 13. Week 1, 14, 27 and 40 result in 0, week 2, 15, 28 and 41 in 1, etc.
    intWeek13 = (intWeek - 1) Mod 13

    The next two lines calculate the row and column number of the upper left corner of the area where the data should be pasted. You'll see that the height and width of the data range come into it; the +2 for lngRow reflects that we start in row 2, and the +27 for lngCol that we start in column AA (= column 27). To be honest, I created these expressions partly by trial and error, it took a fair bit of fiddling to get them right. I used the fact that the comparison (intWeek13 = 12) results in either True or False, and that True = -1 and False = 0 in Visual Basic. The calculation could have been made more transparent but longer by using If ... Then ... End If statements.
    lngRow = (intWeek * 3 13 + (intWeek13 = 12)) * lngHeight + 2
    lngCol = (intWeek13 Mod 4 - 4 * (intWeek13 = 12)) * lngWidth + 27

    Copy the range with upper left corner A2, consisting of lngHeight rows and lngWidth columns, and paste it to the cell whose coordinates we calculated.
    Range("A2").Resize(lngHeight, lngWidth).Copy Cells(lngRow, lngCol)
    End Sub

  15. #15
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy data from one area to another (windows xp)

    Hans,
    MANY MANY THANKS, however, when I was reading your explanation of the code I did not understand this:
    Since the year is divided into 13 week periods (4+4+5), we calculate the remainder of the week number (after subtracting 1) when divided by 13. Week 1, 14, 27 and 40 result in 0, week 2, 15, 28 and 41 in 1, etc.
    intWeek13 = (intWeek - 1) Mod 13

    Could you explain what this means? I don't mean to be a pain, just trying to understand.
    Thanks again,
    B

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •