Pasting in next blank row - VBA

[Fitxsip]

Hi all,

I have a report that pulls data from 4 table (2009,2010,2011 & 2012) and runs a macro to pull them into one 'combined' table.
A series of pivot tables feed from this combined sheet.

I need to get the macro to paste the 2009 data, then find the next blank row and paste the 2010 data under that and so on...

I have been using
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
to find the next row, but all that is happening is that I am getting four rows of 2009 data and all 2012 data then. There should be about 20,000 rows of data for each year.

Here is the code I have been using - as you will see, my knowledge of VBA is quite small. Any help will be greatly appreciated.
Thanks!


Sheets("Combined").Select
Range("A1").Select
Application.Goto Reference:="Table_Query_from_STReports_19"
Selection.Copy
Sheets("Combined").Select
ActiveSheet.Paste
Sheets("Data2009").Select
ActiveWindow.SmallScroll Down:=-3


Application.Goto Reference:="Table_Query_from_STReports_18"
Application.CutCopyMode = False
Selection.Copy
Sheets("Combined").Select


Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Application.Goto Reference:="Table_Query_from_STReports_1"
Application.CutCopyMode = False
Selection.Copy
Sheets("Combined").Select
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

Application.Goto Reference:="Table_Query_from_STReports_110"
Selection.Copy
Sheets("Combined").Select
Range("A1").Select
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste

 

[AlphaFrog]

This...
Range("A1").End(xlDown).Offset(1, 0)
...finds the first blank cell below A1 which is not necessarily the last used cell in column A if you have some blank cells in between data.

Try something like this instead.
Range("A" & Rows.Count).End(xlUp).Offset(1).Select

 

[Fitxsip]

ray:That works perfectly, thanks!

 

[acidhalide1]

Thanks AlphaFrog, this worked great for my macro, and is much easier than indexing the rows with a number.

 

[Moe Kadhom]

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?

Option Explicit

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

 

[AlphaFrog]

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.