VBA code in Macro to copy data from one sheet to another without pasting formulas

Sarah2Ryan · Mar 11th 2016

hi,

I need some help please with my VBA code. Totally new to VBA and Macro's!
I have a Worksheet which is recording a list of tasks a team member plans and actually completes each day.
The sheet has formulas to calculate the task time and insert dates etc.
I have created a macro which copies the data in certain cells and pastes just the values to another worksheet.
I then have a macro which clears these cells and they can be used again the next day to input their planned and actual tasks.
Problem is, when I extract the next days information, it is leaving a hugh gap between todays and tomorrows listed tasks. It is not looking for the next available line but inputting at the end of the last copy. So for example, a user has the option to input 50 tasks (certain cells prefilled with formulas which populate if other cells are filled). If the user only inputs 5 tasks, it still copies the 50 lines over to my new workbook but as there are only formulas and no data in 45 of the lines, it pastes blanks. But on the next copy, it starts at line 51 instead of line 6. Hope this makes sense!
Here is the code I've input so far:

Code

Sub Updatebutton()


Sheets("Team Member 1").Range("B18:G50", "I18:o50").Copy
Sheets("Team member 1 - Detail").Range("A6000").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteFormats
Sheets("Team Member 1 - Detail").Range("A6000").End(xlUp).Offset(2, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False


Application.CutCopyMode = False
      
End Sub

Display More

Any help greatly appreciated.
Kind regards,
Sarah

shknbk2 · Mar 12th 2016

Re: VBA code in Macro to copy data from one sheet to another without pasting formulas

Sarah,

It does make sense. There are lots of websites dealing with questions on how to return a truly empty cell with no direct solution, only workarounds.

So, in a workaround for your case, some additional code could be added to clear the blank cells after pasting the data.

Code

Sub Updatebutton()
    Dim copyTarget As Range, i As Integer, j As Integer
    
    Sheets("Team Member 1").Range("B18:G50", "I18:o50").Copy
    With Sheets("Team member 1 - Detail")
        Set copyTarget = .Cells(.Rows.Count, 1).End(xlUp).Offset(2, 0)
        copyTarget.PasteSpecial Paste:=xlPasteFormats
        copyTarget.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False
        Application.CutCopyMode = False
    
        j = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        For i = j - 1 To copyTarget.Row Step -1
            If .Cells(i, 1).Value <> "" Then
                Exit For
            End If
        Next i
        .Rows(i + 1 & ":" & j).Delete
      End With
End Sub

Display More

Sarah2Ryan · Mar 12th 2016

Re: VBA code in Macro to copy data from one sheet to another without pasting formulas

That worked perfectly, thank you so much!

VBA code in Macro to copy data from one sheet to another without pasting formulas

Participate now!

Share

Users Viewing This Thread