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