Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > copy entire row and paste values only to another sheet
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default copy entire row and paste values only to another sheet
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!!!
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default copy entire row and paste values only to another sheet
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!!!
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default copy entire row and paste values only to another sheet
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!!!
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default copy entire row and paste values only to another sheet
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!!!
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 13
Default copy entire row and paste values only to another sheet
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!!!
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default copy entire row and paste values only to another sheet
You didn't specify the error you are getting. I don't know wherre you are
decaring ws2, ws3, ... I suspect the sheets specified in the ranges are non
on the same page and causing an error. See my comments below and my new
code. I put the destination sheet in only one place in the code so you only
have to make one change when going from one module to a 2nd module.

Set wksPasteTo = Sheets("Closed_Requests")

Remove Line - No need to select
---------------------------------------
Sheets("Closed_Requests").Select
-----------------------------------

'ActiveSheet.Unprotect pw

Add sheet reference
------------------------------------------------
from
LR = Range("B" & Rows.Count).End(xlUp).Row
to
LR = wksPasteTo.Range("B" & Rows.Count).End(xlUp).Row

-------------------------------------------------


Set rngPasteTo = wksPasteTo.Range("A" & (LR + 1))

move inside with and add page reference
-------------------------------------------------
Sheets("Distribution").Select
LastRow = Range("A65536").End(xlUp).Row
-------------------------------------------------

With Sheets("Distribution")
-------------------------------------------------
from
LastRow = Range("A65536").End(xlUp).Row
to
LastRow = .Range("A65536").End(xlUp).Row
------------------------------------------

For x = LastRow To 1 Step -1
If Range("B" & x).Value = "Closed" Then

add sheet reference
------------------------------------------
from
Range("B" & x).EntireRow.Copy
to
.Range("B" & x).EntireRow.Copy
------------------------------------------

Don't need with - sheet already specified in range above
With Sheets("Closed_Requests")
rngPasteTo.PasteSpecial Paste:=xlPasteValues
Set rngPasteTo = rngPasteTo.Offset(1)
End With

remove sheet refernce - already in with
------------------------------------------
from
Sheets("Distribution").Range("B" & x).EntireRow.Delete
to
.Range("B" & x).EntireRow.Delete
------------------------------------------
End If
Next x
End With




New Code

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

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

For x = LastRow To 1 Step -1
If .Range("B" & x).Value = "Closed" Then
.Range("B" & x).EntireRow.Copy

rngPasteTo.PasteSpecial Paste:=xlPasteValues
Set rngPasteTo = rngPasteTo.Offset(1)

.Range("B" & x).EntireRow.Delete
End If
Next x
End With



"drewship" wrote:

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!!!
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Use loop to filter, copy/paste to new sheet; run through entire li ryguy7272 Excel Programming 2 November 25th 08 03:27 PM
If Font.ColorIndex = 3, Copy Entire Row and Paste to New Sheet ryguy7272 Excel Programming 3 November 2nd 07 06:45 PM
Find Multiple Values, Copy Entire Row & Paste ryguy7272 Excel Programming 10 September 27th 07 10:48 PM
Copy Paste Values - Entire Workbook and Save Scott Campbell[_2_] Excel Discussion (Misc queries) 1 August 9th 07 07:53 PM
Macro - copy entire row and paste at bottom of another sheet miker1999 Excel Programming 4 January 31st 04 05:28 PM


All times are GMT +1. The time now is 12:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement