CODEWITHSUNDEEP
vbaexcel
JaReg
JaReg

Reputation: 127

VBA Macro not pasting correctly

I created a VB macro that combines data from 2 sheets into a single sheet so that it can be printed and given out as an overview of the projects we are working on.

What it should (And did in the past) is to go to Sheet 1, copy all the data from A3 to R of the last row that had data in column A.

It then pastes that data starting in A3 of the combined worksheets.

Then it copies the data the same way from Worksheet 2 and pastes it after the last used row in the combined worksheet.

I made some adjustments recently and now the macro doesn't seem to be working.

It pastes the first worksheet correctly (Lean Projects), but the second worksheet's data (Kaizen) isn't copying correctly.

Instead of copying all the data down to the final input row, it copies all the data starting after the number of rows in the first worksheet. (Example: if there are 24 entries in worksheet 1, worksheet 2 is beginning to copy at row 25.

Sub CreateCombinedSheet()

'Assign the worksheets to their respective variables
Set wsCombined = Sheets("Combined (View)(Macro)")
Set wsProjects = Sheets("Lean Projects (View)")
Set wsKaizen = Sheets("Kaizen (View)")

'Clear the Combined worksheet before repopulating it if there is data present

'If the first cell of data, A3, is not empty
If wsCombined.Range("A3") <> "" Then

    'Then clear all rows after row 3 until the last row
    wsCombined.Range("A3", wsCombined.Range("A1048576").End(xlUp).Address).EntireRow.Delete

End If

'Copy all the data in the Lean Projects worksheet
wsProjects.Range("A3", wsProjects.Range("R3").End(xlDown).Address).Copy

'Paste the Lean Projects data into the Combined worksheet
wsCombined.Range("A3").PasteSpecial

'Copy all the data in the Kaizen worksheet
wsKaizen.Range("A3", wsKaizen.Range("R3").End(xlDown).Address).Copy

'Paste the Kaizen data into the Combined worksheet starting in the row after the currently last used row

wsCombined.Range("A" & wsCombined.Range("A" & Rows.Count).End(xlUp).Row + 1).PasteSpecial


End Sub

If anyone can tell where I went wrong, I'd appreciate the help.

Thanks,

Example: enter image description here

Update 11/18:

I commented out the Second Paste function so I could see for myself exactly what was being copied. I found that it is copying the correct data from the second worksheet. The issue comes when I paste that data after the last row in the combined worksheet (After the first sheet's data ends). When I manually past there, I see all the second sheet data as it should be. Then a second later, it appears to collapse upward and Im left with the data starting at row 25 of the second sheet to the last row (28) then the formula on sheet 2 is extrapolated further up to row 50 on the combined sheet.

Upvotes: 1

Views: 7870

Answers (2)

Chrismas007
Chrismas007

Reputation: 6105

Try adding a xlPasteValues after each of your .PasteSpecial code lines

wsCombined.Range("A3").PasteSpecial xlPasteValues

And

wsCombined.Range("A" & wsCombined.Range("A" & .Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteValues

Upvotes: 1

cronos2546
cronos2546

Reputation: 1106

Corrections (in my opinion)

If wsCombined.Range("A3") > 0 Then

Should become

IF wsCombined.Range("A3").Value <> "" Then

Don't use a custom function for the End function. It's redundant, and limits what you can do with it. 

wsCombined.Rows("3:" & LastRow(wsCombined)).ClearContents

Should become 

wsCombined.Range("A3",WSCombined.Range("A1048576").End(xlup).Address).EntireRow.Delete

Another 

wsProjects.Range("A3:" & "R" & LastRow(wsProjects)).Copy

Should become

wsProjects.Range("A3",wSProject.Range("R3").End(xlDown).Address).Copy

And so and so forth. I will circle back to this answer shortly, but I would imagine that if you remove your custom function and use the built in functionality for this, the error will go away.

Upvotes: 1

Related Questions