VBA help request: copy a range, add new data within the range and paste beneath data

[pk247]

Hi All,

I hope someone can help me with some elegant vba code please in order to do the following:

(ps I tried recording a macro but I am quite sure someone here would be able to write something way better and more reliable than my recorded macro...)

Process request:
1- Data is located in Sheet1, columns A:Z - the number of rows is unknown but should never exceed 10,000 rows (I'm thinking xldown for this?)
2- Column M will always have data in it down to the last row of the range (other rows may have the odd blank cell)
3- From Cell A2 go down to last row in range and copy all data within columns A:Z
4- Below the last row paste the same data as above
5- BUT in the newly pasted data, in column K, drag down the word located in Sheet 2 cell B2 >>> Cell B2 is variable string and that's why it needs to pull this word down through column K copied data
6- End process confirming how many rows were copied in a message box

I really hope this make sense and that someone here on this forum could help me out at all please? It's part of a bigger process but I'm trying to take all manual parts out to help save me some time.

Any help is appreciated.

Thanks!
Paul, Ireland

[ashleyuk1984]

I'm just going to give you some guidance which will assist you with your finished code.

1. LastRow = Range("M" & Rows.Count).End(xlUp).Row
2. Number 1 answers this.
3. Range("A2:Z" & LastRow).Copy
4. Range("A" & LastRow).Offset(1, 0).PasteSpecial xlPasteAll
5.
6. MsgBox "Number of rows copied = " & LastRow

I'll let someone else answer 5. I'm watching TV :P lol

[pk247]

Thank you Ashleyuk!

I think this will definitely give me a good start :) With regards step 5. though I'm thinking that if I copied the data to another sheet then took the value of sheet 2 B2 and dragged it down through column K AND THEN copy paste that data under the original data in sheet1 should do the trick. I'll give this a try over the weekend but if anyone else reading this post can think of a more robust way to do this I'm open to ideas...

Thank goodness for this Forum and to all of you who know no limits to Excel vba!

Paul, Ireland

[SamT]

#5

Dim TopOfNew_K As Range
Set TopOfNew_K = Range("K" & LastRow + 1)
'
'#1 thru #4
'

Range(TopOfNew_K, Range("K" & Rows.Count).End(xlUp)) = Sheets("Sheet2").Range("B2")

'Almost same thing. Caution! Fails if blanks in New K Range
'Range(TopOfNew_K, TopOfNew_K.End(xlDown)) = Sheets("Sheet2").Range("B2")


Alternate, Same as above

Range(Range("K" & LastRow + 1), Range("K" & Rows.Count).End(xlUp)) = Sheets("Sheet2").Range("B2")