Copy data from multiple sheets using next available row

[lhansen]

Hi,
I have two sheets that I am trying to copy the information into a MASTER BLOCK sheet without overwriting the previously transmitted information from another sheet. I have created a Button on each titled "Transmit" that the employee can click once they are done entering information into their worksheet (they add information to their own sheet throughout the entire day, so I figured it would be easiest for them to just Transmit the information onto the MASTER BLOCK sheet once all their information is input). I don't necessarily want to specify the range on the Master sheet in which the information will be dumped, as sometimes the BM sheet has no information, so there's nothing to transmit to the Master sheet.
The script I have attached to the Transmit Button on 'BM' sheet is (I recorded this macro but need to figure out how to modify to make it compatible with the other):

Sub BM()
'
' BM Macro
' transmits block entries from BM's sheet to Master sheet
'
'
Range("A7:L51").Select
Selection.Copy
Sheets("MASTER BLOCK").Select
Range("A7:L51").Select
ActiveSheet.Paste
End Sub

I have the same script for 'CD' sheet--even though I am wanting these to go to the next available row so that they don't overwrite each other...that's where my hang up is, I'm not sure what to replace my ranges with, I suppose; or how to say "next available row"

Is there also a way to track which sheet the information came from? I have a column titled "Added By" (column L) that it would be nice to have listed which sheet the information came from.

Thanks for any help you can offer!

 

[mumps]

Try:

Sub BM()
    Range("A7:L51").Copy Sheets("MASTER BLOCK").Cells(Sheets("MASTER BLOCK").Rows.Count, "A").End(xlUp).Offset(1, 0)
End Sub

Since column L is already included in the copied range, does that not already include the "Added By" column? Please clarify.

 

[lhansen]

Oh, my fault. I guess I would only want to include through column K from each individual sheet-I will modify that. This is working well! Thank you!!

Sorry, I would want column L on the Master sheet to generate the sheet name from which the information was pulled from

 

[mumps]

Try:

Sub BM()
    Range("A7:K51").Copy Sheets("MASTER BLOCK").Cells(Sheets("MASTER BLOCK").Rows.Count, "A").End(xlUp).Offset(1, 0)
    Sheets("MASTER BLOCK").Cells(Sheets("MASTER BLOCK").Rows.Count, "L").End(xlUp).Offset(1, 0).Resize(45) = ActiveSheet.Name
End Sub

 

[lhansen]

Try:

Sub BM()
    Range("A7:K51").Copy Sheets("MASTER BLOCK").Cells(Sheets("MASTER BLOCK").Rows.Count, "A").End(xlUp).Offset(1, 0)
    Sheets("MASTER BLOCK").Cells(Sheets("MASTER BLOCK").Rows.Count, "L").End(xlUp).Offset(1, 0).Resize(45) = ActiveSheet.Name
End Sub

this has put the 'BM' in all rows of column L

 

[mumps]

Where exactly did you want the sheet name?

 

[lhansen]

Where exactly did you want the sheet name?

Just in the cells that are brought over from each sheet. For instance, when I clicked transmit on the BM sheet, it copied two rows of information into my master sheet; but instead of just those two rows that were copied over having "BM" in the 'added by' column, the entire column L has "BM"

 

[mumps]

I tried the macro on some dummy data and it worked properly. Delete all the data in your MASTER BLOCK sheet and run this macro:

Sub test()
    MsgBox Range("L" & Rows.Count).End(xlUp).Row
End Sub

What is the result?

 

[lhansen]

I guess I don't know how to just run that... sorry I am quite new to VBA itself.

 

[lhansen]

I think because it is copying the information from sheet BM cell range A7:K51, it is dumping the sheet name into cells L7:L51 even if sheet BM doesn't have information in those rows. I currently only have information in rows 7 & 8, but it is generating the sheet name into rows 7-51 in column K because that's the range of the cells it's copying?
Does that sound right?