Results 1 to 7 of 7

VBA Code Paste Next empty Cell other sheet

This is a discussion on VBA Code Paste Next empty Cell other sheet within the Excel Questions forums, part of the Question Forums category; Hello, I am trying to create a Macro to Paste a selected range (A1:F48) from on worksheet ("Sheet1") to another ...

  1. #1
    New Member
    Join Date
    Jan 2011
    Location
    Dublin
    Posts
    6

    Default VBA Code Paste Next empty Cell other sheet

    Hello,

    I am trying to create a Macro to Paste a selected range (A1:F48) from on worksheet ("Sheet1") to another one ("Sheet5") in the same workbook.
    The "Sheet5" is empty, and i'd like the macro (When run for the 1st time) to paste the copied range from "Sheet1" in the cell A1 of "Sheet5".
    For the 2nd, 3rd, 4th... Xth time, i'd like it to paste the selected range at the last cell with something in it (in the column A of "Sheet5").

    Here is the idea of the code that doesn't work because it's an empty worksheet and i cannot tell it to go paste in A1:

    Sheets("Sheet1").Select
    Range("A1").Select
    Range("A1:F48").Select
    Selection.Copy
    Sheets("Sheet5").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste

    Thanks in advance for your help.

    Kind Regards,
    Guillaume.

  2. #2
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    6,343

    Default Re: VBA Code Paste Next empty Cell other sheet

    Welcome to the board.
    Sheets("Sheet1").Copy

    With Sheets("Sheet5")
    If .Range("A1") = "" Then
    .Range("A1").Paste
    Else
    .Range("A1".EndxlDown).Offset(1,0).Paste
    End If
    End With
    Neil

  3. #3
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,867

    Default Re: VBA Code Paste Next empty Cell other sheet

    Try

    Code:
    Sheets("Sheet1").Range("A1:F48").Copy
    With Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .PasteSpecial Paste:=xlPasteColumnWidths
        .PasteSpecial Paste:=xlPasteValues
    End With
    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    Board Regular dave3009's Avatar
    Join Date
    Jun 2006
    Location
    Glasgow, Scotland
    Posts
    6,292

    Default Re: VBA Code Paste Next empty Cell other sheet

    Hi and welcome

    try

    Code:
    Dim lst As Long
    Sheets("Sheet1").Range("A1:F48").Copy
    With Sheets("Sheet5")
        lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & lst).PasteSpecial xlPasteColumnWidths
        .Range("A" & lst).PasteSpecial xlPasteValues
    End With
    Please state your version of Excel, I use Excel 2007 on Win 7.
    Back up all data before testing VBA codes, and please use [code] tags.
    davehouston.co.uk/

  5. #5
    New Member
    Join Date
    Jan 2011
    Location
    Dublin
    Posts
    6

    Default Re: VBA Code Paste Next empty Cell other sheet

    Hi Guys,

    Thanks a lot for your help!
    The last one seem to work the best, the problem only is that i would like to paste the Values indeed, but as well the Formats of the table that is being copied.

    Is that possible?

    Thanks again!

  6. #6
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    62,867

    Default Re: VBA Code Paste Next empty Cell other sheet

    Try

    Code:
    Sheets("Sheet1").Range("A1:F48").Copy
    With Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .PasteSpecial Paste:=xlPasteColumnWidths
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    New Member
    Join Date
    Jan 2011
    Location
    Dublin
    Posts
    6

    Default Re: VBA Code Paste Next empty Cell other sheet

    Cool, It does work!!!

    Thanks a lot for your helps!!

    Kind Regards,
    Guillaume.

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