+ Reply to Thread
Results 1 to 5 of 5

VBA Need proper Offset syntax with variables, copy then paste on different worksheet

Hybrid View

Old-One VBA Need proper Offset syntax... 03-13-2014, 10:00 PM
Richard Buttrey Re: VBA Need proper Offset... 03-13-2014, 10:14 PM
Old-One Re: VBA Need proper Offset... 03-13-2014, 11:38 PM
Richard Buttrey Re: VBA Need proper Offset... 03-14-2014, 06:06 AM
Old-One Re: VBA Need proper Offset... 03-14-2014, 07:46 AM
  1. #1
    Registered User
    Join Date
    03-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    5

    VBA Need proper Offset syntax with variables, copy then paste on different worksheet

    I can't seem to figure out the proper "Offset" syntax. I keep getting a syntax error.
    I want to represent the equivalent of the following as a variable range using Offset:

    Range("G10:R10").Select
    Selection.Copy

    I currently have:

    Range(Cells(StringLocRow, CopyCol), Cells(StringLocRow, CopyCol.Offset(0, 11))).Select
    Selection.Copy

    Where StringLocRow = the active row (10), and CopyCol = G

    Also, once the proper syntax is developed, I want to copy the values and paste the range of values on a different worksheet - preferably by selecting a single cell on the new worksheet and filling in the range like I would do it manually in Excel.

    Any assistance would be greatly appreciated. Thanks in advance.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Need proper Offset syntax with variables, copy then paste on different worksheet

    Hi,

    Try

     Range(Sheet1.Range(CopyCol & StringLocRow), Sheet1.Range(CopyCol & StringLocRow).Offset(0, 11)).Copy
        Sheet2.Range("A1").PasteSpecial (xlPasteValues)
    Change references to Sheet1 & Sheet2 as necessary.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    03-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: VBA Need proper Offset syntax with variables, copy then paste on different worksheet

    Hi Richard,
    Thanks for the code. That looks like it should work and it makes sense, but I get a "Select method of Range class failed Ending Sub" error message.
    Any ideas what that might be or what i should do differently?
    I appreciate your efforts on my behalf.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: VBA Need proper Offset syntax with variables, copy then paste on different worksheet

    Quote Originally Posted by Old-One View Post
    Hi Richard,
    Thanks for the code. That looks like it should work and it makes sense, but I get a "Select method of Range class failed Ending Sub" error message.
    Any ideas what that might be or what i should do differently?
    I appreciate your efforts on my behalf.
    Hi,

    There was no .Select element to the code I posted. I rarely use .Select since it's hardly ever necessary.

    If you've added it then I suggest you leave it out. What's the code line that's giving you the error. It's usually caused by selecting something which isn't on the sheet which happens to be active at the time: another good reason for not using it.

  5. #5
    Registered User
    Join Date
    03-09-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    5

    Re: VBA Need proper Offset syntax with variables, copy then paste on different worksheet

    Oops, you were correct. I forgot to erase my previous code. Your solution works fine now.
    Thanks so much!

+ 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. Copy from Array in ActiveWorkbook to ThisWorkbook; Transpose copy, Offset Paste
    By sbradley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2013, 05:17 PM
  2. Replies: 1
    Last Post: 03-13-2013, 02:27 PM
  3. Loop to check 2 variables then copy/paste entire row to new worksheet
    By patrick riley in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-02-2012, 02:10 PM
  4. Replies: 3
    Last Post: 04-13-2012, 12:26 PM
  5. [SOLVED] copy and paste between xls files.. syntax problems
    By dr chuck in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-26-2006, 11:15 AM

Tags for this Thread

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