Thanks!! That works for the first part of my question.

For the second part, I have tried:

With ws2
.Range("A3:G" & LR).SpecialCells(xlCellTypeVisible).Copy
Sheets("All_Report").Range("A3").PasteSpecial Paste:=xlPasteValues

With ws2
.Range("A3:G" & LR).SpecialCells(xlCellTypeVisible).Copy
Sheets("All_Report").Range("A3") PasteSpecial Paste:=xlPasteValues

With ws2
.Range("A3:G" & LR).SpecialCells(xlCellTypeVisible).Copy
Sheets("All_Report").Range.PasteSpecial Paste:=xlPasteValues("A3")

With ws2
.Range("A3:G" & LR).SpecialCells(xlCellTypeVisible).Copy
Sheets("All_Report").PasteSpecial Paste:=xlPasteValues.Range("A3")

With ws2
.Range("A3:G" & LR).SpecialCells(xlCellTypeVisible).Copy
Sheets("All_Report").Cells("A3").PasteSpecial Paste:=xlPasteValues

and probably a couple more. I have 6 report modules based on this code which
I think is bloating the spreadsheet with unnessary code copied with the data.
I would guess that it is erroring out because of the .Range() but that is
only a guess.

Thoughts on how to modify this to work?

Thanks again!!
Andrew

"Joel" wrote:

I see what was wrong. wksPasteTo is a worksheet object and rngPasteTo is a
range object.

originally

from
wksPasteTo.Paste rngPasteTo
to
wksPasteTo.PasteSpecial Paste:=xlPasteValues


correction

from
wksPasteTo.Paste rngPasteTo
to
rngPasteTo.PasteSpecial Paste:=xlPasteValues



"drewship" wrote:

Thanks for replying Joel.

I tried that but the Paste:= is hilighted and a 'Compile error: Named
argument not found' is displayed. Do I need to DIM 'Paste:=xlPasteValues', or
part of it ?

The variable 'rngPasteTo' is the cell location for the row to be pasted and
I can not figure out how to meld it into 'wksPasteTo.PasteSpecial
Paste:=xlPasteValues' without getting an error.

"Joel" wrote:

mke this change

from
wksPasteTo.Paste rngPasteTo
to
wksPasteTo.PasteSpecial Paste:=xlPasteValues

"drewship" wrote:

Hello all.

I have a module with the following code snippet I have been trying to modify:

Set wksPasteTo = Sheets("Closed_Requests")
Sheets("Closed_Requests").Select
'ActiveSheet.Unprotect pw
LR = Range("B" & Rows.Count).End(xlUp).Row
Set rngPasteTo = wksPasteTo.Range("A" & (LR + 1))

Sheets("Distribution").Select
LastRow = Range("A65536").End(xlUp).Row

With Sheets("Distribution")
For x = LastRow To 1 Step -1
If Range("B" & x).Value = "Closed" Then
Range("B" & x).EntireRow.Copy
With Sheets("Closed_Requests")
wksPasteTo.Paste rngPasteTo
Set rngPasteTo = rngPasteTo.Offset(1)
End With
Sheets("Distribution").Range("B" & x).EntireRow.Delete
End If
Next x
End With

I have seen several posts that use:

Sheets("Sheet1").Cells.SpecialCells(xlTextValues). EntireRow.Copy
Sheets("Sheet2").Cells.PasteSpecial Paste:=xlPasteValues

but I have been unable to figure out how to merge the above with my code.
There is one column 'B' that contains a color that I want to copy with the
data, but all the rest of the combo boxes and code need to be stripped from
the copied rows.

I have another module with the following different code snippet that I need
to copy and paste as above:

'create temporary worksheet
Set AllName1 = Worksheets.Add(After:=Sheets(Sheets.Count))
AllName1.Name = frmALL.AllName.Value

Set ws2 = ActiveSheet
ws2.Range("A1:" & colName & 1).Value = ws1.Range("A1:" & colName &
13).Value

With ws1
.Range("A2:" & colName & 1).Copy

With ws2.Range("A3:" & colName & 1)
ActiveSheet.Paste
.RowHeight = 12
Range("A3").Select

End With

'compares and copies data
With Source
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
For x = 2 To LastRow
If IsDate(.Cells(x, "C").Value) And .Cells(x, "C").Value < "" And
.Cells(x, "C").Value = lodate And .Cells(x, "C").Value <= hidate Then
If RowsWithNumbers Is Nothing Then
Set RowsWithNumbers = .Cells(x, "C")
Else
Set RowsWithNumbers = Union(RowsWithNumbers, .Cells(x, "C"))
End If
End If
Next
If Not RowsWithNumbers Is Nothing Then

'copies matched data to temporary sheet named by user
RowsWithNumbers.EntireRow.Copy AllName1.Range("A3")

End If

End With

'Clear old report
Sheets("All_Report").Range("A3:J" & Rows.Count).Clear

'Filter data based on dates chosen
LR = Range("A" & Rows.Count).End(xlUp).Row

'Copy data ranges
With ws2
.Range("A3:G" & LR).SpecialCells(xlCellTypeVisible).Copy
Sheets("All_Report").Range("A3")
With ws2
.Range("J3:K" & LR).SpecialCells(xlCellTypeVisible).Copy
Sheets("All_Report").Range("H3")
With ws2
.Range("O3:O" & LR).SpecialCells(xlCellTypeVisible).Copy
Sheets("All_Report").Range("J3")

End With
End With
End With

Any help with these would be greatly appreciated!!!!
Thanks!!!