Paste range to first column, then next blank column
Results 1 to 8 of 8

Thread: Paste range to first column, then next blank column

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Paste range to first column, then next blank column

    Hi All,

    I have found my formula for pasting a copied range to the next blank column, but need some help if the next blank column is the first column in the sheet. With my current formula it pastes to column B not A.

    Sheets("DataRaw").Select
    Application.Goto Reference:="R2C1:R7000C2,R2C7:R7000C7"
    Selection.Copy

    Sheets("DatesRaw").Range("A3").End(xlToLeft).Offset(0, 1).PasteSpecial xlValues
    Application.CutCopyMode = False
    Many thanks
    Last edited by horizonflame; Aug 17th, 2019 at 11:43 AM.

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Paste range to first column, then next blank column

    Hi,
    How about this?

    Code:
    DIM LastCol&
    Dim OffsetNo&
    Sheets("DataRaw").Select
    Application.Goto Reference:="R2C1:R7000C2,R2C7:R7000C7"
    Selection.Copy
    
    LastCol=Sheets("DatesRaw").Range("A3").End(xlToLeft).Column
    If LastCol = 1 and Sheets("DatesRaw").Range("A1")="" then
        OffsetNo=0
    Else
        OffsetNo=1
    End if
    Sheets("DatesRaw").Range("A3").End(xlToLeft).Offset(0, OffsetNo).PasteSpecial xlValues
    Application.CutCopyMode = False

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,817
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Paste range to first column, then next blank column

    This line
    Code:
    Sheets("DatesRaw").Range("A3").End(xlToLeft).Offset(0, 1).PasteSpecial xlValues
    will paste into B3 regardless of how many columns have data.
    Will row 3 always have data?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Sep 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste range to first column, then next blank column

    Quote Originally Posted by Fluff View Post
    Will row 3 always have data?
    Hi Fluff,

    Row 1 and 2 are headers. Row 3 starts empty.

    Thanks

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,817
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Paste range to first column, then next blank column

    In that case try
    Code:
    With Sheets("DatesRaw")
       If .Cells(3, 1) = "" Then
          .Cells(3, 1).PasteSpecial xlPasteValues
       Else
          .Cells(3, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
       End If
    End With
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Sep 2018
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Paste range to first column, then next blank column

    Thanks Fluff, that works great!

    Thank you Mentor82 for your solution

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,817
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Paste range to first column, then next blank column

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Paste range to first column, then next blank column

    My pleasure. I'm glad we could help you. Thansk for you feedback

Some videos you may like

User Tag List

Tags for this Thread

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
  •