| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help with Code - Copy & Paste
I'm looking for an easier way to modify the following code. The data in
worksheet2 varies at times too, i.e., column A:A may hold 20 records or 200. Any help is greatly appreciated. Sub Macro1() Sheets("Sheet2").Select Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A4").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
If your intention is to copy the contents of the A column in Sheet2 to the A column in Sheet1 you could use:
Worksheets("Sheet2").Range("A:A").Copy _ Destination:=Worksheets("Sheet1").Range("A1") -- John Green - Excel MVP Sydney Australia "JStone0218" wrote in message ... I'm looking for an easier way to modify the following code. The data in worksheet2 varies at times too, i.e., column A:A may hold 20 records or 200. Any help is greatly appreciated. Sub Macro1() Sheets("Sheet2").Select Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A4").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
John,
I'm looking to copy & paste cell A1, A2, A3... from Worksheet2 through the last active cell in column A, to Worksheet1, cell A1, one record at a time. Worksheet1, A1 is the data input range for a dynamic web query. I appreciate your help. Donnie If your intention is to copy the contents of the A column in Sheet2 to the A column in Sheet1 you could use: Worksheets("Sheet2").Range("A:A").Copy _ Destination:=Worksheets("Sheet1").Range("A1") John Green - Excel MVP Sydney Australia Sub Macro1() Sheets("Sheet2").Select Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A4").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
Why do you want to copy it one cell at a time?
It is noticeably faster to do it the way John suggested. What do you want to do in the source sheet if there is not value? Skip that cell or copy it anyway? If you want to skip it then what do you want to do with that cell on the destination sheet? Leave it the way it was or clear the contents of it? If you want to clear the contents of it then you are saying that Col A in both sheets will have the same data in them at the end - so it would be faster to copy the whole column. Chrissy. JStone0218 wrote John, I'm looking to copy & paste cell A1, A2, A3... from Worksheet2 through the last active cell in column A, to Worksheet1, cell A1, one record at a time. Worksheet1, A1 is the data input range for a dynamic web query. I appreciate your help. Donnie If your intention is to copy the contents of the A column in Sheet2 to the A column in Sheet1 you could use: Worksheets("Sheet2").Range("A:A").Copy _ Destination:=Worksheets("Sheet1").Range("A1") John Green - Excel MVP Sydney Australia Sub Macro1() Sheets("Sheet2").Select Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A4").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
Why do you want to copy it one cell at a time?
Worksheet1, A1 is the data input range for a dynamic web query. What do you want to do in the source sheet if there is not value? There will always be a value... |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
JStone0218 wrote
Why do you want to copy it one cell at a time? Worksheet1, A1 is the data input range for a dynamic web query. What do you want to do in the source sheet if there is not value? There will always be a value... Not from what you said in your first post. You said there that there could be 20 or 200 values. If there are only 20 then there are 180 more that do not have a value. If you copy all of col A then you do not have to worry about how many values there are. I do not think there is any reason why you would do it one cell at a time. Chrissy. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
The problem with this solution is that the range changes from OP The data in worksheet2 varies at times too, i.e., column A:A may hold 20 records or 200. And if it contained 20 this time and 200 last time then you will have 180 extra values in Sheet1. Chrissy. <Gord Dibben wrote in message ... If data in column A is contiguous(no blanks) then...... Sub copyover() Application.ScreenUpdating = False Worksheets("Sheet2").Select Range(Range("A1"), Range("A1").End(xlDown)).Copy _ Destination:=Sheets("Sheet1").Range("A1") Application.CutCopyMode = False Application.ScreenUpdating = True End Sub If non-contiguous in column A then.......... Worksheets("Sheet2").Select Range(Range("A1"), Range("A65000").End(xlUp)) _ .Copy Destination:=Sheets("Sheet1").Range("A1") I would tend to go with second option. Blanks will not become an issue. Gord Dibben XL2002 On 11 Oct 2003 00:29:09 GMT, (JStone0218) wrote: I'm looking for an easier way to modify the following code. The data in worksheet2 varies at times too, i.e., column A:A may hold 20 records or 200. Any help is greatly appreciated. Sub Macro1() Sheets("Sheet2").Select Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A2").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A3").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A4").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste Sheets("Sheet2").Select Range("A5").Select Application.CutCopyMode = False Selection.Copy Sheets("Sheet1").Select ActiveSheet.Paste End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy and paste using vb code | Excel Discussion (Misc queries) | |||
| copy and paste formula using vb code | Excel Discussion (Misc queries) | |||
| VBA-code for search,copy and paste | Excel Discussion (Misc queries) | |||
| copy and paste code problem | Excel Worksheet Functions | |||
| copy and paste from different sheets into one sheet using a VB code | Excel Programming | |||
Hybrid Mode
