copy range between sheets
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 --- Message posted from http://www.ExcelForum.com/ |
copy range between sheets
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 -- Don Guillett SalesAid Software "chick-racer" wrote in message ... 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 --- Message posted from http://www.ExcelForum.com/ |
copy range between sheets
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. --- Message posted from http://www.ExcelForum.com/ |
copy range between sheets
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 -- Don Guillett SalesAid Software "chick-racer" wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
copy range between sheets
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 -- Regards, Tom Ogilvy "chick-racer" wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
copy range between sheets
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. --- Message posted from http://www.ExcelForum.com/ |
copy range between sheets
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") -- Don Guillett SalesAid Software "chick-racer" wrote in message ... 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. --- Message posted from http://www.ExcelForum.com/ |
All times are GMT +1. The time now is 02:49 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com