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
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