Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to paste into a worksheet
Dear All,
I am using the below to search, copy + paste data into a worksheet. The code finds the results OK, but isn't pasting anything, and just jumping straight into the MsgBox. Any ideas what I've done wrong anyone?? Yours ever so hopefully ;-) Andy W ------------ Sub BBBBB() Dim rng As Range Dim bCopy As Boolean Sheets("Raw Data").Activate If ActiveSheet.AutoFilterMode Then _ ActiveSheet.AutoFilterMode = False With Range("A1") .AutoFilter Field:=2, Criteria1:="=*Z*" End With With ActiveSheet.AutoFilter.Range Set rng = .Offset(1, 0).Resize(.Rows.Count - 1) bCopy = False If .SpecialCells(xlVisible).Columns(1) _ .Rows.Count 1 Then _ bCopy = True End With If bCopy Then rng.Copy Destination:=Sheets("CustbyRDC").Range("A7") Else MsgBox "No rows to copy" End If ActiveSheet.AutoFilterMode = False End Sub .. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to paste into a worksheet
there shouldn't be a .rows.count. It should just be a .count. My mistake.
Try this one: (worked for me). Sub BBBBB() Dim rng As Range Dim bCopy As Boolean Sheets("Raw Data").Activate If ActiveSheet.AutoFilterMode Then _ ActiveSheet.AutoFilterMode = False With Range("A1") .AutoFilter Field:=2, Criteria1:="=*Z*" End With With ActiveSheet.AutoFilter.Range Set rng = .Offset(1, 0).Resize(.Rows.Count - 1) bCopy = False If .Columns(1).SpecialCells(xlVisible) _ .Count 1 Then _ bCopy = True End With If bCopy Then rng.Copy Destination:=Sheets("CustbyRDC").Range("A7") Else MsgBox "No rows to copy" End If ActiveSheet.AutoFilterMode = False End Sub -- Regards, Tom Ogilvy "Andy W" wrote in message ... Dear All, I am using the below to search, copy + paste data into a worksheet. The code finds the results OK, but isn't pasting anything, and just jumping straight into the MsgBox. Any ideas what I've done wrong anyone?? Yours ever so hopefully ;-) Andy W ------------ Sub BBBBB() Dim rng As Range Dim bCopy As Boolean Sheets("Raw Data").Activate If ActiveSheet.AutoFilterMode Then _ ActiveSheet.AutoFilterMode = False With Range("A1") .AutoFilter Field:=2, Criteria1:="=*Z*" End With With ActiveSheet.AutoFilter.Range Set rng = .Offset(1, 0).Resize(.Rows.Count - 1) bCopy = False If .SpecialCells(xlVisible).Columns(1) _ .Rows.Count 1 Then _ bCopy = True End With If bCopy Then rng.Copy Destination:=Sheets("CustbyRDC").Range("A7") Else MsgBox "No rows to copy" End If ActiveSheet.AutoFilterMode = False End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy & paste worksheet with print settings into new worksheet | Excel Discussion (Misc queries) | |||
Cut and Paste from one worksheet to another? | Excel Discussion (Misc queries) | |||
Need to cut and paste worksheet | New Users to Excel | |||
excel 97: copy and paste values from one worksheet to another worksheet | Excel Programming | |||
excel 97: copy and paste values from one worksheet to another worksheet | Excel Programming |