Macro for button - copy and paste rows

C

cbrd

I have the following macro assigned to a button ("New Grass Week") in
the "Grass Cutting" sheet. It is suppose to, upon clicking, take data
from the "Customers" sheet and paste it into the proper columns in the
"Grass Cutting" sheet. It doesnt do anything when clicked. Im pretty
sure I have all the offsets and column numbers down, I just think there
something missing or wrong with the first part of the code. Any ideas?


URL of the .xls sheet
http://www.ashleylandscaping.com/excel-example.xls

This code was made by "Barrie Davidson". I added a few things and
edited it a bit to fit my needs for this situation. Worked fine before
I did this, Thanks Barrie!



Code:
--------------------
Sub NewGrassWeek()
Dim FilterRange As Range, CopyRange As Range

If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
ActiveSheet.Rows("3:" & Application.WorksheetFunction.Max(3, Range("A65536").End(xlUp).Row)).ClearContents
Set FilterRange = Sheets("Customers").Range("A1:M" & _
Sheets("Customers").Range("A1").End(xlDown).Row)
Set CopyRange = FilterRange.Offset(1, 2).Resize(FilterRange.Rows.Count - 1, 1)
FilterRange.AutoFilter Field:=1, Criteria1:=CStr(Target)
FilterRange.AutoFilter Field:=9, Criteria1:="y"
Application.CutCopyMode = False
On Error Resume Next
CopyRange.SpecialCells(xlCellTypeVisible).Copy
Range("A3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
CopyRange.Offset(, 1).SpecialCells(xlCellTypeVisible).Copy
Range("B3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
CopyRange.Offset(, 2).SpecialCells(xlCellTypeVisible).Copy
Range("C3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
CopyRange.Offset(, 3).SpecialCells(xlCellTypeVisible).Copy
Range("D3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
CopyRange.Offset(, 4).SpecialCells(xlCellTypeVisible).Copy
Range("E3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
CopyRange.Offset(, 5).SpecialCells(xlCellTypeVisible).Copy
Range("F3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
CopyRange.Offset(, 6).SpecialCells(xlCellTypeVisible).Copy
Range("G3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
CopyRange.Offset(, 7).SpecialCells(xlCellTypeVisible).Copy
Range("H3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
CopyRange.Offset(, 9).SpecialCells(xlCellTypeVisible).Copy
Range("L3").PasteSpecial xlPasteValues
Application.CutCopyMode = False
FilterRange.AutoFilter
Target.Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top