Results 1 to 4 of 4

VBA copy and paste with dynamic ranges

This is a discussion on VBA copy and paste with dynamic ranges within the Excel Questions forums, part of the Question Forums category; Hi So I am trying to move data from one spreadsheet into another; I'm new to VBA and I need ...

  1. #1
    New Member
    Join Date
    Mar 2013
    Posts
    2

    Question VBA copy and paste with dynamic ranges

    Hi

    So I am trying to move data from one spreadsheet into another; I'm new to VBA and I need some help writing the code for this macro.



    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+A
    '
    Sheets("Canada").Select
    Range("F851:F887").Select
    Selection.Copy
    Sheets("Alberta").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=True
    End Sub

    What I need is to loop this, and the next time it selects from "Canada" the range changes so that it selects the
    next 37 entries (for example the next one would be F888-F924) and so on...until it hits an empty cell.

    I also need the macro to paste the next selection (F888-F924) into the sheet "Alberta" as normal except one row below the row it previously pasted data into.

    Any help would be greatly appreciated!!

  2. #2
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,767

    Default Re: VBA copy and paste with dynamic ranges

    Hi, meister,
    WELCOME to MrExcel!

    This should be close to what you need. We don't know your skills and knowledge, but I hope you will take the time to analyse this, so you can learn and progress. Good luck!
    Code:
    Option Explicit
    Sub test()
    Dim LastRow As Long
    Dim i As Long
    Dim j As Long
    
    j = 1
    Application.ScreenUpdating = False
    
        With Sheets("Canada")
        LastRow = .Range("F" & Rows.Count).End(xlUp).Row
            For i = 851 To LastRow Step 37
            j = j + 1
            .Range("F" & i & ":F" & i + 36).Copy
            Sheets("Alberta").Range("A" & j).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            Next i
        End With
    
    Application.ScreenUpdating = True
    End Sub
    kind regards,
    Erik

  3. #3
    New Member
    Join Date
    Mar 2013
    Posts
    2

    Default Re: VBA copy and paste with dynamic ranges

    Thanks a lot! I'm going to go ahead and work with this.

  4. #4
    MrExcel MVP erik.van.geit's Avatar
    Join Date
    Feb 2003
    Location
    Belgium 3272 Testelt
    Posts
    17,767

    Default Re: VBA copy and paste with dynamic ranges

    You're welcome
    Feel free to come back if you have any problem.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com