Modify VBA Code when Pasting

[MikeL]

Hello,
I have code that copies data from columns A-K of one spreadsheet and pastes into columns A-K of another.

The issue is that I would like to exclude the data in Column I from pasting...ie paste with blanks instead. I would like to modify on the paste side of the code and not in the initial selection if possible.

Code
'Selects col A-K of one spreadsheet
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:K" & LR).SpecialCells(xlCellTypeVisible).Select
Selection.Copy


'Pastes to Consolidation WB...
Can this paste data in col A-K but put override col I with blanks...? Cavaet...There are rows above this pasted data in Col I that shouldn't be deleted. I just want to exclude from the Paste selection

Consolidation.Activate
With Sheets("Sheet1")
.Range("A1").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteAll
End With


Thanks Mike

 

[VoG]

How about

Consolidation.Sheets("Sheet1").Columns("I").Delete

 

[MikeL]

The cavaet is that this is related to appending data to existing records. I don't want to delete all of Col I. Just the part of the range A-K that I am pasting.

Mike

 

[AlphaFrog]

Dim LR As Long, Nextrow As Long

LR = Range("A" & Rows.Count).End(xlUp).Row
Nextrow = Consolidation.Sheets("Sheet1").Range("A1").End(xlDown).Offset(1).Row

Range("A2:H" & LR).SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Consolidation.Sheets("Sheet1").Range("A" & Nextrow)
Range("J2:K" & LR).SpecialCells(xlCellTypeVisible).Copy _
    Destination:=Consolidation.Sheets("Sheet1").Range("J" & Nextrow)

 

[Rick Rothstein]

Maybe something like this (note the introduction of a new variable)...

Consolidation.Activate
With Sheets("Sheet1")
  NewDataStartRow = .Range("A1").End(xlDown).Offset(1)
  .Range("A1").End(xlDown).Offset(1).PasteSpecial Paste:=xlPasteAll
  .Range(.Cells(NewDataStartRow, "I"), .Cells(Rows.Count, "I").End(xlUp)).Clear
End With

 

[MikeL]

Rick,
I understand what the code is doing but it errors out on the line

#.Range(.Cells(NewDataStartRow, "I"), .Cells(Rows.Count, "I").End(xlUp)).Clear#


Mike

 

[Rick Rothstein]

Rick,
I understand what the code is doing but it errors out on the line

#.Range(.Cells(NewDataStartRow, "I"), .Cells(Rows.Count, "I").End(xlUp)).Clear#

See if this works instead...

With Sheets("Sheet1")
  NewDataStartRow = .Cells(Rows.Count, "A").End(xlUp).Offset(1).Row
  .Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll
  .Range(.Cells(NewDataStartRow, "I"), .Cells(Rows.Count, "I").End(xlUp)).Clear
End With

 

[MikeL]

That worked - Thanks Rick

AlphaFrog - I got your code to work as well

Mike