+ Reply to Thread
Results 1 to 9 of 9

Copy Range X times Based on User Entered #

Hybrid View

  1. #1
    Registered User
    Join Date
    09-26-2015
    Location
    Here Or There
    MS-Off Ver
    2010
    Posts
    3

    Copy Range X times Based on User Entered #

    Hello all,

    I am looking to create a macro that copies data from A3:D3 and paste it X times on a new speadsheet (X: this number is entered in cell cell J3 by the user).

    All of the light orange on the Check's Sheet would be filled in by the user and the Server Pay Sheet would be populated with 1 line for each server (J3) working the event.

    I would like the sheet to look like "Should Look Like This" sheet. And i want the Macro to run 1 line at a time if possible (So I can run it each time I have a new event).

    Thanks in advance for any assistance!

    Test Copy.xlsm

  2. #2
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,549

    Re: Copy Range X times Based on User Entered #

    It is real handy to attach a file that needs a password.

  3. #3
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,549

    Re: Copy Range X times Based on User Entered #

    While you're looking for your password, try this on a copy of your workbook.

    Sub Maybe()
        Dim lr As Long, i As Long, b As Long, a(), j As Long
        lr = Range("A3").End(xlDown).Row
        For i = 3 To lr
            b = Cells(i, 10).Value
            a() = Array(Cells(i, 1).Value, Cells(i, 2).Value, Cells(i, 3).Value, Cells(i, 4).Value, Cells(i, 10).Value, Cells(i, 9).Value / b)
            For j = 1 To b
                With Sheets("Server Pay Sheet")
                    .Range("A" & .Rows.Count).End(xlUp)(2).Resize(, 6) = a
                End With
            Next j
        Next i
    End Sub
    Run it from the "Check's" Sheet.

  4. #4
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Copy Range X times Based on User Entered #

    I agree with jolivanes. It is considered bad form. Here is another option:

    Sub Server_Pay()
    Dim ws1 As Worksheet:   Set ws1 = Sheets("Check's")
    Dim ws2 As Worksheet:   Set ws2 = Sheets("Server Pay Sheet")
    Dim i As Long
    Dim servers As Integer
    
    Application.ScreenUpdating = False
    
    For i = 3 To ws1.Range("J" & Rows.Count).End(xlUp).Row
        servers = ws1.Range("J" & i).Value
        ws1.Range("A" & i, "D" & i).Copy ws2.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(servers, 4)
        ws2.Range("E" & Rows.Count).End(xlUp).Offset(1).Resize(servers).Value = servers
        ws2.Range("F" & Rows.Count).End(xlUp).Offset(1).Resize(servers).Value = CDbl(ws1.Range("I" & i).Value / servers)
    Next i
    
    Application.ScreenUpdating = True
    End Sub
    If you are happy with my response please click the * in the lower left of my post.

  5. #5
    Registered User
    Join Date
    09-26-2015
    Location
    Here Or There
    MS-Off Ver
    2010
    Posts
    3

    Re: Copy Range X times Based on User Entered #

    Sorry guys, the password is "password". They both work for what i'm looking for so thank you.
    One last thing would be is there a way I can run it and have it only copy over new data? For example if I ran it with the current Check's sheet, and then I added 1 more row below with new data to the Check's Sheet, could it only copy over that 1 row of new data?

    Thanks for your help

  6. #6
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,549

    Re: Copy Range X times Based on User Entered #

    Clear the "Server Pay Sheet" first would be the proper way to go in case something is changed in the existing data.
    Run the macro again on all data.
    If that is not possible, let us know.

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,549

    Re: Copy Range X times Based on User Entered #

    Just in case you want to go with clearing "Server Pay Sheet" first, something like this would probably do it.
    This code can be run from any sheet. You don't have to be in "Check's" sheet

    Sub Maybe_A()
        Dim lr As Long, i As Long, b As Long, a(), j As Long, sh1 As Worksheet, sh2 As Worksheet
        Set sh1 = Sheets("Check's")
        Set sh2 = Sheets("Server Pay Sheet")
    
        sh2.Range("A1:A" & sh2.Cells(Rows.Count, 1).End(xlUp).Row).Offset(2).Resize(, 7).ClearContents
        lr = sh1.Cells(Rows.Count, 10).End(xlUp).Row
    
        For i = 3 To lr
            b = sh1.Cells(i, 10).Value
            a() = Array(sh1.Cells(i, 1).Value, sh1.Cells(i, 2).Value, sh1.Cells(i, 3).Value, sh1.Cells(i, 4).Value, sh1.Cells(i, 10).Value, sh1.Cells(i, 9).Value / b)
            For j = 1 To b
                With sh2
                    .Range("A" & .Rows.Count).End(xlUp)(2).Resize(, 6) = a
                End With
            Next j
        Next i
    End Sub

  8. #8
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,549

    Re: Copy Range X times Based on User Entered #

    Here is another one. Includes the servers.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    09-26-2015
    Location
    Here Or There
    MS-Off Ver
    2010
    Posts
    3

    Re: Copy Range X times Based on User Entered #

    Those are some great macros! I think everything is setup and I am good. Thanks for everyone's help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy Paste a Range of Values from a Different Sheet Based on Value Entered in Cell
    By hamidxa in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-13-2015, 04:53 PM
  2. Count # times word entered in cell based of other critera
    By hambly in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-08-2015, 10:38 AM
  3. [SOLVED] Print same page, multiple times based on dates entered
    By DixieLou in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2014, 04:32 PM
  4. Replies: 5
    Last Post: 09-08-2013, 10:09 AM
  5. Need to copy specific cells into an existing worksheet based on date entered by user
    By jrfleury in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2011, 09:44 AM
  6. Copy user entered Range from one sheet to another
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2006, 10:30 AM
  7. Replies: 0
    Last Post: 02-24-2005, 04:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1