copy range between sheets

[chick-racer]

I would like to know how I can copy ranges between sheets.
What i have for code right now doesnt work at all.
Could someone give advice? Thank you.

here's what i have so far:

roundNumber = InputBox("Enter the round number for which you would like
summary for.", "Summary")
K = 4
For J = 4 To 700

If Worksheets(1).Cells(J, "C") = roundNumber Then

Set rng = Worksheets(1).Range("F" & J, "O" & J)
Set newrng = Worksheets(4).Range("C" & K, "L" & K)
newrng = rng
K = K + 1
End If

Next J

 

[Don Guillett]

try this

Sub copyifnumber()
Dim roundnumber As Integer
roundnumber = InputBox("Enter the round number for which you would like
summary for.", "Summary")
K = 4
For J = 4 To 700
If Worksheets(1).Cells(J, "C") = roundnumber Then
Worksheets(4).Range("C" & K, "L" & K).Value = _
Worksheets(1).Range("F" & J, "O" & J).Value
K = K + 1
End If
Next J
End Sub

 

[chick-racer]

thanks alot. I dont know why i didnt think of trying that.
Now, is there a line i can add in order to keep the same formatting. I
guess what i'm trying to say is, I have some cells with shading and/or
colored font, is there a way to make it look the same when it's copied
to the next worksheet?

Much appreciated.

 

[Don Guillett]

It would have been nice had you mentioned that to start with. The way you
had it written you were doing VALUES.

Sub PasteALL()
Dim roundnumber As Integer
roundnumber = InputBox("Enter the round number for which you would like
summary for.", "Summary")
K = 4
For J = 4 To 700
If Worksheets(1).Cells(J, "C") = roundnumber Then
Worksheets(1).Range("F" & J, "O" & J).Copy
Worksheets(4).Range("C" & K).PasteSpecial Paste:=xlPasteAll
K = K + 1
End If
Next J
End Sub

 

[Tom Ogilvy]

Worksheets(1).Range("F" & J, "O" & J).copy _
Worksheets(4).Range("C" & K)


if your source contains formulas, but you want to only transfer the
resulting values

Worksheets(1).Range("F" & J, "O" & J).copy
Worksheets(4).Range("C" & K).PasteSpecial xlValues
Worksheets(4).Range("C" & K).PasteSpecial xlFormats

 

[chick-racer]

thank you very much for the help.. works like a charm!

Now i have one final question. Is there a way to make a range that has
columns missing?

For example, I would like the data in Columns A,B,F thru O, Q,R

since there are some columns on the origional sheet that i do not want
to copy and paste to the new worksheet.

 

[Don Guillett]

if you use Range("A13,B13,F13,J13") and copy to another sheet it will
copy into a,b,c,d so you would have to use several range copy's to get what
you want
Range("A13,B13")
Range("F13")
Range("J13")