How to alter copy/paste locations in this macro

[mikeyboy]

Hi, This macro works wonderfully thanks to Tom. I will need to alter it in the near future to skip a column in the copy and also on the paste. The commands to select and paste

Sheets("Pumping Report").Range("C" & _
PlaceCntr & ":H" & PlaceCntr) = _
Range("A" & Cntr & ":F" & Cntr).Value

are obvious but how to customise it myself to skip columns is not. Perhaps with a little explanation I can alter this myself.

Thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LastEntry As Long, Cntr As Long
Dim CheckDate As Variant
Dim IsCheckDate As Date
Dim PlaceCntr As Integer

LastEntry = Cells(Rows.Count, 1).End(xlUp).Row
PlaceCntr = 29
Sheets("Pumping Report").Range("C30:H1000").ClearContents
For Cntr = 11 To LastEntry
CheckDate = Cells(Cntr, 1) & " " & Format(Cells(Cntr, 2), "HH:MM")
If IsDate(CheckDate) Then
IsCheckDate = CheckDate
If Now <= IsCheckDate Then
PlaceCntr = PlaceCntr + 1
Sheets("Pumping Report").Range("C" & _
PlaceCntr & ":H" & PlaceCntr) = _
Range("A" & Cntr & ":F" & Cntr).Value
End If
End If
Next
End Sub

[NateO]

Not sure when you say "skip" a column what you mean. If you want to offset the entire selection by a column to the right then take the following:

Sheets("Pumping Report").Range("C" & _ 
PlaceCntr & ":H" & PlaceCntr) = _ 
Range("A" & Cntr & ":F" & Cntr).Value

And change
-C to D
-H to I
-A to B
-F to G

The letters are the column identifiers.

Hope this helps.

[mikeyboy]

Thanks for the reply Nate. Actually what I'm referring to is how would I change the code to copy from A,B, D and F and paste to "Pumping Report" C,D, F,G. As you can see the cells are not in order now and my attempts to reword the script have failed.

[NateO]

You could split up the code to look at individual columns, since you're not selecting anything, it should still be timely.

Sheets("Pumping Report").Range("a" & _
PlaceCntr & ":b" & PlaceCntr) = _
Range("c" & Cntr & ":d" & Cntr).Value
Sheets("Pumping Report").Range("d" & _
PlaceCntr) = Range("f" & Cntr).Value
Sheets("Pumping Report").Range("f" & _
PlaceCntr) = Range("g" & Cntr).Value

Hope this helps.

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-29 16:37 ]

[mikeyboy]

Thanks Nate, That was very helpful. Don't understand why I can only have two column letters

Range("A" & Cntr & ":B" & Cntr).Value

Why not something like this?

Range("A","B","D" & Cntr & "F" & Cntr).Value

Here is what I ended up with that works.

Sheets("Pumping Report").Range("C" & _
PlaceCntr & ":D" & PlaceCntr) = _
Range("A" & Cntr & ":B" & Cntr).Value
Sheets("Pumping Report").Range("F" & _
PlaceCntr) = Range("D" & Cntr).Value
Sheets("Pumping Report").Range("G" & _
PlaceCntr) = Range("F" & Cntr).Value