Macro for Button: If Cell = Name then Copy to Another Spreadsheet

[koolgeex]

Something like:
In spreadsheet "Complete Backlog", if a cell in Column K = "Open" AND the cell in Column G of the same row = "Snodgress" THEN copy cells A?:J? and paste into cells A?:J? of the "Snodgress" sheet. Of course skipping to the next available row and also the content should not repeat itself.

I have this code so far but it gives me an error about merged cells. I think that's because i have miscellaneous text outside of the designated "paste area". So the whole row shouldn't be pasted, just the specific range. Any suggestions?

Sub copy_director_snod()
Dim i As Long, lrow As Long, lastrow As Long
With Worksheets("Complete Backlog")
lrow = .Range("a" & .Rows.Count).End(xlUp).Row
For i = 2 To lrow
If .Range("g" & i).Value = "Snodgress" Then
lastrow = Worksheets("Snodgress").Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & i & ":L" & i).Copy Worksheets("Snodgress").Range("A" & lastrow + 1)
a = a + 1
End If
Next i
End With
End Sub

 

[Gary's Student]

What is:

a = a + 1

trying to accomplish??

 

[koolgeex]

Trying to get it to move to the next available row in the Snodgress spreadsheet.

 

[Ingolf]

Hi,

Try this:

Sub copy_director_snod()
Dim i As Long, lrow As Long, lastrow As Long
With Worksheets("Complete Backlog")
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lrow
If   .Range("G" & i).Value = "Snodgress" AND  .Range("K" & i).Value = "Open" Then
lastrow = Worksheets("Snodgress").Range("A" & Rows.Count).End(xlUp).Row
.Range("A" & i & ":J" & i).Copy Worksheets("Snodgress").Range("A" & lastrow + 1)
End If
Next i
End With

 

[koolgeex]

Thanks. I received an error that says "Cannot Change Part of a Merged Cell" and it highlights the following line to debug:

.Range("A" & i & ":J" & i).Copy Worksheets("Snodgress").Range("A" & lastrow + 1)

The only thing that may have merged cells is below row 30 of the Snodgress sheet. So i would need the copied cells to paste into a specific area -- [A2 through A30 only]

 

[koolgeex]

Anyone?

 

[Ingolf]

Hi,

Not a good idea to use merged cells.

I try in a sheet with merged cells in row 30 and code works well.
Check your sheet

 

[koolgeex]

Hm. Okay, so I see that it actually did copy the data but it started pasting in Row 38 for whatever reason. That's why it ran into my formulas and merged cells and such. Any idea why it did that?

 

[Ingolf]

Rows above row 30 are empty? or blank as return of formula?

 

[koolgeex]

They are empty except for the headers in row 1. I do I have validation drop down lists in a few columns though.