VBA Code Paste Next empty Cell other sheet

guilli_003

New Member
Joined
Jan 18, 2011
Messages
6
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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:

http://www.mrexcel.com/forum/excel-...paste-next-empty-cell-column.html#post4177977
http://www.mrexcel.com/forum/excel-...-paste-into-next-open-column.html#post4177976
http://www.mrexcel.com/forum/excel-...copy-paste-next-blank-column.html#post4177975
http://www.mrexcel.com/forum/excel-...-next-empty-cell-other-sheet.html#post4177972
http://www.mrexcel.com/forum/excel-questions/584509-pasting-next-blank-row-visual-basic-applications.html#post4177970


Forum Rules
21.) Avoid multiple questions of a similar nature. Duplicate posts by the same user will be locked and/or deleted when found.
 
Upvote 0
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

Rich (BB 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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top