Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for button - copy and paste rows
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 -------------------- -- cbrd ------------------------------------------------------------------------ cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009 View this thread: http://www.excelforum.com/showthread...hreadid=498060 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A macro to copy & paste many rows (a range) to the next column .. | New Users to Excel | |||
copy the command button and macro for multiple rows | Excel Discussion (Misc queries) | |||
why will my copy and paste button not work | Excel Worksheet Functions | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
copy & paste command button | Excel Programming |