Copy and Paste only filled cells from a dynamic range to another sheet in empty cells

[seanpcorbett1]

Excuse my naivety, but any assistance would be greatly appreciated. Basically I have two columns of data within a range that I want to paste into two empty columns within a dataset, I've used the following code for a start, but it lacks the ability to only select filled sells from the DealTemplate and won't paste in the empty rows in the DealList.sheet. I've attached an example sheet. I've had this macro in my sheets before, but for the life of me I can't remember the code that tweaks it to do what I need. Thanks in advance for any help!!!!

Sub CopyPaste_Click()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("DealTemplate")
Set pasteSheet = Worksheets("DealList")

copySheet.Range("y5:z49").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

[Mumps1]

Try:

Please Login or Register  to view this content.

[seanpcorbett1]

Worked great, but I need to be able to continue pasting down in the DealList sheet as I update the Deal template... I.e. the macro needs to find the next empty cells in B/C and paste the deal numbers into those cells. Also, I need it to only paste the number of rows containing data from DealTemplate as copying the whole range seems to paste all 20 rows from the DealTemplate to the Deal List...

[Mumps1]

This macro will take care of pasting the data to the first empty cell in column B. I don't quite follow what you mean by the second part of your question. In the file you posted there are 16 rows with data in the range Y5:Y20 in sheet "DealTemplate" and the macro is copying/pasting only those 16 rows not 20 rows. Please clarify.

Please Login or Register  to view this content.

[seanpcorbett1]

So this will be a dynamic spreadsheet, with every day a new list of deals being inputed. Deal numbers will be manually entered into the deal template sheet each day. Each day I would like to hit the copy paste button and paste the new deal numbers into the DealList sheet. Then the next day the process repeats and the macro copies and pastes that days new deal numbers under the previous days' deals. Maybe that makes a bit more sense?

[Mumps1]

Have you tried the macro in Post# 4. I believe it does what you ask.

[seanpcorbett1]

Ahhhh I see!!! yes it does only issue is that you have to step through it on the DealList sheet, otherwise it gets a runtime error. I'd like to get the button on the DealTemplate sheet and assign the macro to that... I know I'm getting way too picky... thanks for the help so far though!!!! And I'm sure its a simple fix...

[Mumps1]

This version should work from either sheet:

Please Login or Register  to view this content.

[seanpcorbett1]

Yes!!! you my friend are a gentleman and a scholar!!!! Works perfectly!!! thanks so much!!!

[Mumps1]

My pleasure.