Page 1 of 2 12 LastLast
Results 1 to 10 of 14
Like Tree2Likes

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,868

    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 offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    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
    Inverclyde, Scotland
    Posts
    6,316

    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 offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651

    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.

  8. #8
    New Member
    Join Date
    Apr 2015
    Posts
    31

    Default Re: VBA Code Paste Next empty Cell other sheet

    Hi
    I was wondering if you can help me.

    I have below VBA code that goes through multiple files and copy rang A1-C60 from all the sheets and paste them into a master workbook with one sheet. The macro is ran from that master workbook.

    I need to run this macro every month, at the moment The data are pasted in the next available row. So my data are always in columns A to C with data running down.

    I want the macro modified so the data are pasted in the next empty column. So the first time the macro is run, the data are pasted in columns A to C, the second time in D to F and so on........

    could you help me?

    Code:
    Option Explicit
    
    
    ' >>>>> Put the initial path where the files to be processed are stored here. _
      End with backslash
    Const sInitialPath = "C:\MyPath\"
    
    
    Sub GetData()
        Dim wbIn As Workbook, wbOut As Workbook
        Dim rIn As Range, rOut As Range
        Dim wsIn As Worksheet, wsOut As Worksheet
        Dim diaFolder As FileDialog
        Dim lCount As Long
    
    
        Set wbOut = ThisWorkbook
        ' Assuming masterWB has only one sheet
        Set wsOut = wbOut.Sheets(1)
        
            '   get file name for file to process
        MsgBox "Select all the files you want to process by using the Ctrl key and the mouse. "
    
        ' Open the file dialog to get the  files
        Set diaFolder = Application.FileDialog(msoFileDialogFilePicker)
        With diaFolder
            .AllowMultiSelect = True
            .InitialView = msoFileDialogViewList
            .InitialFileName = sInitialPath
            lCount = .Show
        End With
        If lCount = -1 Then
        ' for each selected file
        For lCount = 1 To diaFolder.SelectedItems.Count
            
            Set wbIn = Workbooks.Open(diaFolder.SelectedItems(lCount))
    
            'loop through all the sheets in the opened book
            For Each wsIn In wbIn.Sheets
                'set output range on the Mastersheet to last row
                Set rOut = wsOut.Cells(wsOut.Rows.Count, 1).End(xlUp).Offset(1, 0)
                'now copy the values accross to the Mastersheet
                With wsIn.Range("A1:C60")
                    rOut.Resize(.Rows.Count, .Columns.Count).Value = .Value
                End With
            Next wsIn
            'close WB
            wbIn.Close savechanges:=False
        Next lCount
        End If
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        Application.EnableEvents = True
    
        'Cleanup
        Set wbIn = Nothing
        Set wbOut = Nothing
        Set rIn = Nothing
        Set rOut = Nothing
        Set wsIn = Nothing
        Set wsOut = Nothing
        Set diaFolder = Nothing
    End Sub

  9. #9
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    13,711

    Default Re: VBA Code Paste Next empty Cell other sheet

    Quote Originally Posted by Moe Kadhom View Post
    Hi
    I was wondering if you can help me.

    I have below VBA code that goes through multiple files and copy rang A1-C60 from all the sheets and paste them into a master workbook with one sheet. The macro is ran from that master workbook.

    I need to run this macro every month, at the moment The data are pasted in the next available row. So my data are always in columns A to C with data running down.

    I want the macro modified so the data are pasted in the next empty column. So the first time the macro is run, the data are pasted in columns A to C, the second time in D to F and so on........

    could you help me?
    Duplicates:

    VBA code to copy and paste to next empty cell in Column A
    VBA- Paste into next open column
    Macro to Copy and Paste to next blank Column
    VBA Code Paste Next empty Cell other sheet
    Pasting in next blank row - VBA


    Forum Rules
    21.) Avoid multiple questions of a similar nature. Duplicate posts by the same user will be locked and/or deleted when found.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in your menu for the forum editor will apply the CODE tags around your selected text.

  10. #10
    Board Regular
    Join Date
    Dec 2008
    Location
    Tijuana Mexico :)
    Posts
    396

    Default Re: VBA Code Paste Next empty Cell other sheet

    Hi guys,

    Not trying to Hijack this thread but pertains to EXACTLY what i need except i have data in Row 50 and when i run this code it pastes the data below that row on 51.

    And that's offcourse because of that .END statement. is there a way to define the range to go and only search for empty cells from A1 to A49?

    Thanx in advance

    Code:
    Dim lst As LongSheets("Sheet1").Range("A1:F48").Copy
    With Sheets("Sheet5")
        lst = .Range("A" & Rows.Count).End(xlUp).Row + 1
        .Range("A" & lst).PasteSpecial xlPasteValues
    End With
    Windows 7 64 Bit, Excel 2013
    Windows 10 64 Bit Excel 2016 32 Bit.

Page 1 of 2 12 LastLast

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