Share via

Macro help - copy paste data to the end of data range

Anonymous
2011-05-20T15:45:09+00:00

I am copying data by month from a database and pasting each month of data at the end of the previous month (stop point). I would then like to copy or drag the formulas down to the section where the new data is pasted.

For example,

Columns A:N contain pasted data - columns O to X contain formulas.

The first month of data goes from row 2 down to row 1500, then the next month of data needs to be pasted.

My goal is to prevent the need to find that last row and paste the new month of data. Also, my goal is not to need to copy the last row of formulas and drag the formulas down to the last row of the new month's data.

I hope you can help as it will save me quite a bit of time and I would be able to use the macro for other reports as well.

Thank you very much for your help.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-05-20T16:20:39+00:00

Assuming the data to be pasted is in the active worksheet with headings (not to be copied) in row 1 the destination worksheet is called "MonthlyData"

Sub AppendToMonthlyData()

  Dim rLast As Range

  Set rLast = ThisWorkbook.Sheets("MonthlyData").Cells(Rows.Count,1).End(xlUp)

  With Range("A1").CurrentRegion

    .Offset(1).Resize(.Rows.Count-1).Copy

     rLast.Offset(1).PasteSpecial xlAll

     rLast.Cells(1,"O").Resize(.Rows.Count,9).FillDown    ' formulas in 9 columns O to W

  End With

End Sub

Was this answer helpful?

1 person found this answer helpful.
0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2011-05-20T22:23:57+00:00

    I wouldn't have expected there to be a blank line.

    Maybe there was something in column A, like a space, in the row after the previous month's data?  The code goes up from the bottom of column A until it finds a cell with something in.

    It then copies all except the first row of data from the new sheet to the cell below that last cell in column A.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-05-20T17:55:18+00:00

    Is there a way no remove the code that inserts a blank line between the last set of data and the new monthly data?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-05-20T17:10:20+00:00

    xlValues in place of xlAll (note not x1All).

    Was this answer helpful?

    0 comments
  4. Anonymous
    2011-05-20T17:08:55+00:00

    If I want to only copy values insteas of "rLast offset(1).PasteSpecial x1All". How could I modify this sentence?

    Was this answer helpful?

    0 comments