On July 20th, 2005 I posted an inquiry regarding how to "Copy only Visible
Cells of a Variable Range between Worksheets" (See link)

http://www.microsoft.com/office/comm...8d3&sloc=en-us

Now I'm trying to take this concept one step further: I want to Copy only
Visible Cells of a Variable Range on a Variable Worksheet to another Workbook.

The workbooks are "Command.xls" (which contains all the code and two
worksheets; one for the user interface and a second which is our paste
target) and "Travel.xls" (which contains all the data on sheet one with a
subset of that data pasted to sheet two as described in the above link).

At the time the code executes, both workbooks are already open. I've tried
various approaches to this problem, but so far I just end up with a "Runtime
Error 91" Object or With Block not set. Any suggestions?

Damian Carrillo

Hi Damian,

Try something like:

Dim rng As Range
Dim rng1 As Range
Dim destRng As Range
Dim destWB As Workbook
Dim srcWB As Workbook
Dim srcSh As Worksheet
Dim destSh As Worksheet


Set srcWB = Workbooks("Book1.xls") '<<======= CHANGE
Set destWB = Workbooks("Book2.xls") '<<======= CHANGE

Set srcSh = srcWB.Sheets("Sheet1") '<<======= CHANGE
Set destSh = destWB.Sheets("Sheet2") '<<======= CHANGE

Set rng = srcSh.AutoFilter.Range

Set rng1 = rng.SpecialCells(xlCellTypeVisible)

Set destRng = destSh.Range("A1")

rng1.Copy Destination:=destRng

End Sub


---
Regards,
Norman



"Damian Carrillo" wrote in
message ...
On July 20th, 2005 I posted an inquiry regarding how to "Copy only Visible
Cells of a Variable Range between Worksheets" (See link)

http://www.microsoft.com/office/comm...8d3&sloc=en-us

Now I'm trying to take this concept one step further: I want to Copy only
Visible Cells of a Variable Range on a Variable Worksheet to another
Workbook.

The workbooks are "Command.xls" (which contains all the code and two
worksheets; one for the user interface and a second which is our paste
target) and "Travel.xls" (which contains all the data on sheet one with a
subset of that data pasted to sheet two as described in the above link).

At the time the code executes, both workbooks are already open. I've
tried
various approaches to this problem, but so far I just end up with a
"Runtime
Error 91" Object or With Block not set. Any suggestions?

Damian Carrillo

Hi Norman,

I tried to adapt this code to my project but it failed. This is part of a
larger subroutine. Here's what I came up with. Any ideas how I messed it up?

Damian

'Transfers only summary line data from Sheet1 to Sheet2
Dim SourceRange As Range
Dim SpecialRange As Range
Dim TargetRange As Range

Set SourceRange = Sheets(1).AutoFilter.Range
Set SpecialRange = SourceRange.SpecialCells(xlCellTypeVisible)
Set TargetRange = Sheets(2).Range("A1")

SpecialRange.Copy
TargetRange.PasteSpecial , Paste:=xlPasteAll
SpecialRange.Copy
TargetRange.PasteSpecial , Paste:=xlPasteValues
SelectCurrentRegion
Selection.AutoFilter

'Transfers consolidated data from Sheet2 to AirTravelBill Assistant
Dim SourceRange1 As Range
Dim SpecialRange1 As Range
Dim TargetRange1 As Range
Dim TargetWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet

Set SourceWorkbook = Workbooks("Travel.xls")
Set TargetWorkbook = Workbooks("AirTravelBill Assistant.xls")
Set SourceSheet = SourceWorkbook.Sheets(2)
Set TargetSheet = TargetWorkbook.Sheets(2)
Set SourceRange1 = SourceSheet.AutoFilter.Range
Set SpecialRange1 = SourceRange1.SpecialCells(xlCellTypeVisible)
Set TargetRange1 = TargetSheet.Range("A1")

SpecialRange1.Copy Destination:=TargetRange1

"Norman Jones" wrote:

Hi Damian,

Try something like:

Dim rng As Range
Dim rng1 As Range
Dim destRng As Range
Dim destWB As Workbook
Dim srcWB As Workbook
Dim srcSh As Worksheet
Dim destSh As Worksheet


Set srcWB = Workbooks("Book1.xls") '<<======= CHANGE
Set destWB = Workbooks("Book2.xls") '<<======= CHANGE

Set srcSh = srcWB.Sheets("Sheet1") '<<======= CHANGE
Set destSh = destWB.Sheets("Sheet2") '<<======= CHANGE

Set rng = srcSh.AutoFilter.Range

Set rng1 = rng.SpecialCells(xlCellTypeVisible)

Set destRng = destSh.Range("A1")

rng1.Copy Destination:=destRng

End Sub


---
Regards,
Norman



"Damian Carrillo" wrote in
message ...
On July 20th, 2005 I posted an inquiry regarding how to "Copy only Visible
Cells of a Variable Range between Worksheets" (See link)

http://www.microsoft.com/office/comm...8d3&sloc=en-us

Now I'm trying to take this concept one step further: I want to Copy only
Visible Cells of a Variable Range on a Variable Worksheet to another
Workbook.

The workbooks are "Command.xls" (which contains all the code and two
worksheets; one for the user interface and a second which is our paste
target) and "Travel.xls" (which contains all the data on sheet one with a
subset of that data pasted to sheet two as described in the above link).

At the time the code executes, both workbooks are already open. I've
tried
various approaches to this problem, but so far I just end up with a
"Runtime
Error 91" Object or With Block not set. Any suggestions?

Damian Carrillo

Norman,

The following line gives me Runtime Error 91 "Object variable or With Block
variable not set":

Set SourceRange = .Sheets(1).AutoFilter.Range

But clearly it IS set. I can't figure out why it was working before (from
the first time you responded to my post about moving between sheets) and now
it won't.

Any suggestions?

Damian

"Norman Jones" wrote:

Hi Damian, to make that read more clearly:

'=============================
Sub Tester02A()

Dim SourceRange As Range
Dim SpecialRange As Range
Dim TargetRange As Range

Dim SourceRange1 As Range
Dim SpecialRange1 As Range
Dim TargetRange1 As Range
Dim TargetWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet

'\\ Transfers only summary line data from Sheet1 to Sheet2
'\\ in "Travel.xls"

Set SourceWorkbook = Workbooks("Travel.xls")

With SourceWorkbook

Set SourceRange = .Sheets(1).AutoFilter.Range

On Error Resume Next 'In case no cells
found!
Set SpecialRange = SourceRange. _
SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If SpecialRange Is Nothing Then Exit Sub ' Nothing to copy!

Set TargetRange = .Sheets(2).Range("A1")
End With

SpecialRange.Copy
With TargetRange
.PasteSpecial , Paste:=xlPasteAll
.Value = .Value
If Not .Parent.AutoFilterMode Then 'In case Autofilter On
.AutoFilter
End If
End With

'\\ Transfers consolidated data from Sheet2 in Travel.xls
'\\ AirTravelBill Assistant.xls

Set TargetWorkbook = _
Workbooks("AirTravelBill Assistant.xls")
Set SourceSheet = SourceWorkbook.Sheets(2)
Set TargetSheet = TargetWorkbook.Sheets(2)
Set SourceRange1 = SourceSheet.AutoFilter.Range

On Error Resume Next 'In case no
cells found!
Set SpecialRange1 = _
SourceRange1.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If SpecialRange1 Is Nothing Then Exit Sub ' Nothing to copy!

Set TargetRange1 = TargetSheet.Range("A1")

SpecialRange1.Copy Destination:=TargetRange1

End Sub


--

---
Regards,
Norman



"Damian Carrillo" wrote in
message ...
Hi Norman,

I tried to adapt this code to my project but it failed. This is part of a
larger subroutine. Here's what I came up with. Any ideas how I messed it
up?

Damian

'Transfers only summary line data from Sheet1 to Sheet2
Dim SourceRange As Range
Dim SpecialRange As Range
Dim TargetRange As Range

Set SourceRange = Sheets(1).AutoFilter.Range
Set SpecialRange = SourceRange.SpecialCells(xlCellTypeVisible)
Set TargetRange = Sheets(2).Range("A1")

SpecialRange.Copy
TargetRange.PasteSpecial , Paste:=xlPasteAll
SpecialRange.Copy
TargetRange.PasteSpecial , Paste:=xlPasteValues
SelectCurrentRegion
Selection.AutoFilter

'Transfers consolidated data from Sheet2 to AirTravelBill Assistant
Dim SourceRange1 As Range
Dim SpecialRange1 As Range
Dim TargetRange1 As Range
Dim TargetWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet

Set SourceWorkbook = Workbooks("Travel.xls")
Set TargetWorkbook = Workbooks("AirTravelBill Assistant.xls")
Set SourceSheet = SourceWorkbook.Sheets(2)
Set TargetSheet = TargetWorkbook.Sheets(2)
Set SourceRange1 = SourceSheet.AutoFilter.Range
Set SpecialRange1 = SourceRange1.SpecialCells(xlCellTypeVisible)
Set TargetRange1 = TargetSheet.Range("A1")

SpecialRange1.Copy Destination:=TargetRange1

"Norman Jones" wrote:

Hi Damian,

Try something like:

Dim rng As Range
Dim rng1 As Range
Dim destRng As Range
Dim destWB As Workbook
Dim srcWB As Workbook
Dim srcSh As Worksheet
Dim destSh As Worksheet


Set srcWB = Workbooks("Book1.xls") '<<======= CHANGE
Set destWB = Workbooks("Book2.xls") '<<======= CHANGE

Set srcSh = srcWB.Sheets("Sheet1") '<<======= CHANGE
Set destSh = destWB.Sheets("Sheet2") '<<======= CHANGE

Set rng = srcSh.AutoFilter.Range

Set rng1 = rng.SpecialCells(xlCellTypeVisible)

Set destRng = destSh.Range("A1")

rng1.Copy Destination:=destRng

End Sub


---
Regards,
Norman



"Damian Carrillo" wrote in
message ...
On July 20th, 2005 I posted an inquiry regarding how to "Copy only
Visible
Cells of a Variable Range between Worksheets" (See link)

http://www.microsoft.com/office/comm...8d3&sloc=en-us

Now I'm trying to take this concept one step further: I want to Copy
only
Visible Cells of a Variable Range on a Variable Worksheet to another
Workbook.

The workbooks are "Command.xls" (which contains all the code and two
worksheets; one for the user interface and a second which is our paste
target) and "Travel.xls" (which contains all the data on sheet one with
a
subset of that data pasted to sheet two as described in the above
link).

At the time the code executes, both workbooks are already open. I've
tried
various approaches to this problem, but so far I just end up with a
"Runtime
Error 91" Object or With Block not set. Any suggestions?

Damian Carrillo

Hi Norman,

I'm a total dunce. I finally figured out what was going on. It was another
case of "Pay No Attention to the Code Behind the Curtain!" In the code
preceding what I posted, I had set the autofilter not once, but twice on the
same area. The second use of autofilter toggles it off, if one is already
set and no filter parameters are specified in the second autofilter
statement. Thus, when we try to set the source range value, it returns an
error.

Apparently I added the second autofilter set when I copied the code from my
last project to this one. With it gone, the code now works. Thanks very
much for your very timely and well written assistance!

Sincerely
Damian Carrillo

"Norman Jones" wrote:

Hi Damian, to make that read more clearly:

'=============================
Sub Tester02A()

Dim SourceRange As Range
Dim SpecialRange As Range
Dim TargetRange As Range

Dim SourceRange1 As Range
Dim SpecialRange1 As Range
Dim TargetRange1 As Range
Dim TargetWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet

'\\ Transfers only summary line data from Sheet1 to Sheet2
'\\ in "Travel.xls"

Set SourceWorkbook = Workbooks("Travel.xls")

With SourceWorkbook

Set SourceRange = .Sheets(1).AutoFilter.Range

On Error Resume Next 'In case no cells
found!
Set SpecialRange = SourceRange. _
SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If SpecialRange Is Nothing Then Exit Sub ' Nothing to copy!

Set TargetRange = .Sheets(2).Range("A1")
End With

SpecialRange.Copy
With TargetRange
.PasteSpecial , Paste:=xlPasteAll
.Value = .Value
If Not .Parent.AutoFilterMode Then 'In case Autofilter On
.AutoFilter
End If
End With

'\\ Transfers consolidated data from Sheet2 in Travel.xls
'\\ AirTravelBill Assistant.xls

Set TargetWorkbook = _
Workbooks("AirTravelBill Assistant.xls")
Set SourceSheet = SourceWorkbook.Sheets(2)
Set TargetSheet = TargetWorkbook.Sheets(2)
Set SourceRange1 = SourceSheet.AutoFilter.Range

On Error Resume Next 'In case no
cells found!
Set SpecialRange1 = _
SourceRange1.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

If SpecialRange1 Is Nothing Then Exit Sub ' Nothing to copy!

Set TargetRange1 = TargetSheet.Range("A1")

SpecialRange1.Copy Destination:=TargetRange1

End Sub


--

---
Regards,
Norman



"Damian Carrillo" wrote in
message ...
Hi Norman,

I tried to adapt this code to my project but it failed. This is part of a
larger subroutine. Here's what I came up with. Any ideas how I messed it
up?

Damian

'Transfers only summary line data from Sheet1 to Sheet2
Dim SourceRange As Range
Dim SpecialRange As Range
Dim TargetRange As Range

Set SourceRange = Sheets(1).AutoFilter.Range
Set SpecialRange = SourceRange.SpecialCells(xlCellTypeVisible)
Set TargetRange = Sheets(2).Range("A1")

SpecialRange.Copy
TargetRange.PasteSpecial , Paste:=xlPasteAll
SpecialRange.Copy
TargetRange.PasteSpecial , Paste:=xlPasteValues
SelectCurrentRegion
Selection.AutoFilter

'Transfers consolidated data from Sheet2 to AirTravelBill Assistant
Dim SourceRange1 As Range
Dim SpecialRange1 As Range
Dim TargetRange1 As Range
Dim TargetWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim SourceSheet As Worksheet
Dim TargetSheet As Worksheet

Set SourceWorkbook = Workbooks("Travel.xls")
Set TargetWorkbook = Workbooks("AirTravelBill Assistant.xls")
Set SourceSheet = SourceWorkbook.Sheets(2)
Set TargetSheet = TargetWorkbook.Sheets(2)
Set SourceRange1 = SourceSheet.AutoFilter.Range
Set SpecialRange1 = SourceRange1.SpecialCells(xlCellTypeVisible)
Set TargetRange1 = TargetSheet.Range("A1")

SpecialRange1.Copy Destination:=TargetRange1

"Norman Jones" wrote:

Hi Damian,

Try something like:

Dim rng As Range
Dim rng1 As Range
Dim destRng As Range
Dim destWB As Workbook
Dim srcWB As Workbook
Dim srcSh As Worksheet
Dim destSh As Worksheet


Set srcWB = Workbooks("Book1.xls") '<<======= CHANGE
Set destWB = Workbooks("Book2.xls") '<<======= CHANGE

Set srcSh = srcWB.Sheets("Sheet1") '<<======= CHANGE
Set destSh = destWB.Sheets("Sheet2") '<<======= CHANGE

Set rng = srcSh.AutoFilter.Range

Set rng1 = rng.SpecialCells(xlCellTypeVisible)

Set destRng = destSh.Range("A1")

rng1.Copy Destination:=destRng

End Sub


---
Regards,
Norman



"Damian Carrillo" wrote in
message ...
On July 20th, 2005 I posted an inquiry regarding how to "Copy only
Visible
Cells of a Variable Range between Worksheets" (See link)

http://www.microsoft.com/office/comm...8d3&sloc=en-us

Now I'm trying to take this concept one step further: I want to Copy
only
Visible Cells of a Variable Range on a Variable Worksheet to another
Workbook.

The workbooks are "Command.xls" (which contains all the code and two
worksheets; one for the user interface and a second which is our paste
target) and "Travel.xls" (which contains all the data on sheet one with
a
subset of that data pasted to sheet two as described in the above
link).

At the time the code executes, both workbooks are already open. I've
tried
various approaches to this problem, but so far I just end up with a
"Runtime
Error 91" Object or With Block not set. Any suggestions?

Damian Carrillo