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

**seanpcorbett1** · 09-12-2016, 10:09 AM

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** · 09-12-2016, 11:22 AM

Try:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Sheets("DealTemplate").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("DealTemplate").Range("Y5:Z" & bottomA).Copy
    Sheets("DealList").Range("B33").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

**seanpcorbett1** · 09-12-2016, 12:29 PM

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** · 09-12-2016, 01:00 PM

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.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Sheets("DealTemplate").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("DealTemplate").Range("Y5:Z" & bottomA).Copy
    Sheets("DealList").Cells(ActiveSheet.Columns("B:C").Find("*", [B1], xlValues, , xlByRows, xlPrevious).Row, "B").Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

**seanpcorbett1** · 09-12-2016, 01:33 PM

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** · 09-12-2016, 02:32 PM

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

**seanpcorbett1** · 09-12-2016, 03:00 PM

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** · 09-12-2016, 03:19 PM

This version should work from either sheet:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Sheets("DealTemplate").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("DealTemplate").Range("Y5:Z" & bottomA).Copy
    Sheets("DealList").Cells(Sheets("DealList").Columns("B:C").Find("*", [B1], xlValues, , xlByRows, xlPrevious).Row, "B").Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

**seanpcorbett1** · 09-12-2016, 03:22 PM

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

**Mumps1** · 09-12-2016, 03:23 PM

My pleasure.

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Need to find empty filled cells in a column and copy to the empty cell below

Macro code to copy range of cells, paste special on p/word protected sheet and hide empty

how to copy a range of cells, paste them and then find next empty cell

[SOLVED] Copy and Paste Cells to Next empty cell in range

Macro to Copy Range with Merged Cells and Paste in the Empty Row in the Same Sheet

VBA macro to Find a dynamic range of cells, copy then paste to another sheet

[SOLVED] Copy Data From Filled to Empty Cells

Bookmarks

Bookmarks

Posting Permissions