+ Reply to Thread
Results 1 to 4 of 4

VBA Sorting into ranges error.

  1. #1
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    VBA Sorting into ranges error.

    Hi Guys,

    i was just wondering if anyone can help I am using the below code to sort values from column b & c sheet 1 into named ranges defined in column a on sheet 1 on ranges in sheet2 but for some reason keeps on coming up with a runtime error 1004 i have checked through all the references and everything seems to be okay. can anyone offer anyhelp please?



    [Sub CopySheet1A1toSheet2NextCellInColumnA()

    Dim lRow As Long, l As Long
    Dim sRangeName As String
    Dim lLastRow As Long 'Last used row in Column A on Sheet1


    'Last Used Row
    lLastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    'Loop through all used cells on column A with a value
    For l = 1 To lLastRow
    'Copy Cells B & C of the current row On sheet 1
    Sheets("Sheet1").Range("B" & 2 & ":C" & 2).Copy
    'get the name range
    sRangeName = Sheets("Sheet1").Range("A" & 2).Value
    'replace any spaces with underscore:
    sRangeName = Replace(sRangeName, " ", "_")

    'get next empty row in named range
    lRow = 1
    With Sheets("Sheet2").Range(sRangeName).Cells(lRow, 1)
    Do Until .Value = ""
    lRow = lRow + 1
    Loop
    'Paste Data
    .PasteSpecial xlPasteAll
    End With

    Next l

    End Sub]

  2. #2
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: VBA Sorting into ranges error.

    You have a couple syntax errors, but:

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    I'm interested in starting a career working with VBA, if anyone knows of any opportunities!

  3. #3
    Forum Contributor
    Join Date
    09-14-2016
    Location
    Fareham UK
    MS-Off Ver
    2010
    Posts
    110

    Re: VBA Sorting into ranges error.

    Hi,

    where is that # Icon as I cant see it. my appologies as I didnt mean to post it this way.

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: VBA Sorting into ranges error.

    It's in the tool bar as shown.

    It should look like:

    Please Login or Register  to view this content.
    code.png

+ 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. Sorting Date Ranges
    By DannyDresdan in forum Excel General
    Replies: 3
    Last Post: 06-12-2015, 11:29 PM
  2. Help w VBA Sorting Named Ranges
    By jlheath01 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-21-2014, 10:51 AM
  3. Sorting 2 data ranges by comparing one column in each and sorting to match
    By MDKsmiffy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-17-2013, 03:30 PM
  4. Sorting date ranges
    By cclayton in forum Excel General
    Replies: 4
    Last Post: 12-07-2010, 09:41 AM
  5. Sorting Value Ranges
    By inspiresit in forum Excel General
    Replies: 5
    Last Post: 06-15-2010, 05:54 PM
  6. Sorting ranges
    By timbim in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-04-2009, 10:59 AM
  7. [SOLVED] Sorting Multiple Ranges
    By simoneaux in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-08-2005, 12:06 PM

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