+ Reply to Thread
Results 1 to 7 of 7

Macros code for Loop transfer to another sheet

  1. #1
    Registered User
    Join Date
    05-26-2018
    Location
    London,England
    MS-Off Ver
    2016
    Posts
    18

    Macros code for Loop transfer to another sheet

    Hi, can someone me with a macro code

    trying to copy data from a master sheet to sheet2 and have it loop 10x
    wish to copy B4:H63 and paste on to sheet2 on E5:K64 loop 10 times

    then would like to copy J4:K63 and paste on to sheet2 on C5:D64
    then the next of row of columns to copy L4:M63 and paste on to sheet2 where C5:D64 ends

    in that sequence

    can this be possible

    please see attach
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Macros code for Loop transfer to another sheet

    Hi there,

    Welcome to the forum!!

    See how this goes:

    Please Login or Register  to view this content.
    Regards,

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Registered User
    Join Date
    05-26-2018
    Location
    London,England
    MS-Off Ver
    2016
    Posts
    18

    Re: Macros code for Loop transfer to another sheet

    Hi Trebor76

    it worked well l am really pleased but one of columns under was repeated twice and when l looped it 15 times it didn't match.

    Option Explicit
    Sub Macro1()

    Dim lngMyLoop As Long
    Dim lngPasteRow As Long
    Dim varMyCols As Variant
    Dim strMyCols() As String

    Application.ScreenUpdating = False

    For lngMyLoop = 1 To 15
    If lngMyLoop = 1 Then
    Sheets("Master").Range("B5:H63").Copy Destination:=Sheets("Sheet2").Range("E5")
    Else
    lngPasteRow = Sheets("Sheet2").Range("E:K").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Sheets("Master").Range("B5:H63").Copy Destination:=Sheets("Sheet2").Range("E" & lngPasteRow)
    End If
    Next lngMyLoop

    lngMyLoop = 1

    For Each varMyCols In Split("J-K L-M N-O P-Q R-S T-U V-W X-Y Y-Z Z-AA AB-AC AD-AE AF-AG AH-AI AJ-AK AL-AM")
    strMyCols = Split(varMyCols, "-")
    If lngMyLoop = 1 Then
    Sheets("Master").Range(strMyCols(0) & "5:" & strMyCols(1) & "63").Copy Destination:=Sheets("Sheet2").Range("C5")
    Else
    lngPasteRow = Sheets("Sheet2").Range("C:D").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Sheets("Master").Range(strMyCols(0) & "5:" & strMyCols(1) & "63").Copy Destination:=Sheets("Sheet2").Range("C" & lngPasteRow)
    End If
    lngMyLoop = lngMyLoop + 1
    Next varMyCols

    Application.ScreenUpdating = True

    End Sub

  4. #4
    Registered User
    Join Date
    05-26-2018
    Location
    London,England
    MS-Off Ver
    2016
    Posts
    18

    Re: Macros code for Loop transfer to another sheet

    one of columns under header 8 (1 3755) was repeated twice and when l looped it 15 times it didn't match.

  5. #5
    Registered User
    Join Date
    05-26-2018
    Location
    London,England
    MS-Off Ver
    2016
    Posts
    18

    Re: Macros code for Loop transfer to another sheet

    Thanks Trebor76 l have the error in the code had to delete Y-Z
    (SOLVED)

  6. #6
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,523

    Re: Macros code for Loop transfer to another sheet

    Thanks Trebor76 l have the error in the code had to delete Y-Z
    Yes, my bad.

    Please ensure you wrap any code you post with the appropriate tags as I have done in my original post like so [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]. If you could also mark the thread as solved (see the link in my signature how to) it would be appreciated.

    Thank you for the rep

    Regards,

    Robert

  7. #7
    Registered User
    Join Date
    05-26-2018
    Location
    London,England
    MS-Off Ver
    2016
    Posts
    18

    Re: Macros code for Loop transfer to another sheet

    Thanks l am truly grateful Trebor76 and all the experts

+ 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. Macros to transfer data from one sheet to another
    By learningslowly in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-30-2014, 12:06 PM
  2. excel macros to transfer from sheet 2 to sheet 1
    By dilawneko in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-07-2014, 08:37 PM
  3. [SOLVED] Beginner Here: I need Macros to transfer specific data from one sheet to another sheet
    By aleem5 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-08-2013, 12:36 AM
  4. macros not working after sheet transfer to different workbook
    By nerdzkilla in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2012, 01:03 PM
  5. Macros for data transfer - one sheet to another
    By floridavera in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-23-2011, 02:47 AM
  6. Macros to transfer specific data to another sheet?
    By biggie in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-27-2006, 06:24 PM
  7. [SOLVED] Copy the 14 cells and transfer them to another sheet loop
    By Crowbar via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-09-2006, 01:25 AM

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