| 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 |
|
#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?
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 |
|
#6
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 |
|
#7
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... |
|
#8
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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
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, Thanks for your response. I need one cell copied at a time, not the entire column. The reason, I'm working with a dynamic web query that uses cell A1 in worksheet1 as the input data. Again, I want the value in cell A1 in worksheet2 to be copied to A1 in worksheet1. When this cell is changed, the web query updates. Repeat the process, copying data from worksheet2, cell A2, to worksheet1, A1... Any help you can provide would be appreciated. Donnie |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
"JStone0218" wrote in message ... 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, Thanks for your response. I need one cell copied at a time, not the entire column. The reason, I'm working with a dynamic web query that uses cell A1 in worksheet1 as the input data. Again, I want the value in cell A1 in worksheet2 to be copied to A1 in worksheet1. When this cell is changed, the web query updates. Repeat the process, copying data from worksheet2, cell A2, to worksheet1, A1... Any help you can provide would be appreciated. Then you need to do a recalc after each cell is copied. Your recorded macro did not show that you waited after each copy to have the recalc preformed. I believe you need to use a for each loop on the source and include in it the recalc command. As I do not have a dynamic query to test this on I will leave that to you - but you will find it easy with something like this Dim oCell as Range For Each oCell in Worksheets("Sheet2").Range("A:A") application.calculate worksheets("Sheet1").Range(oCell.Address) = oCell Next oCell You can also restrict the range used in col A to only those rows above the last used cell in the workbook Chrissy. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need Help with Code - Copy & Paste
With Worksheets("Sheet2")
set rng = .Range(.Cells(1,1),.Cells(Rows.count,1).End(xlup)) End with for each cell in rng worksheets("Sheet1").Value = cell.value Worksheets("Sheet1").QueryTables(1).Refresh Backgroundquery:=False ' or whatever updates your web query. Next -- Regards, Tom Ogilvy JStone0218 wrote in message ... 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, Thanks for your response. I need one cell copied at a time, not the entire column. The reason, I'm working with a dynamic web query that uses cell A1 in worksheet1 as the input data. Again, I want the value in cell A1 in worksheet2 to be copied to A1 in worksheet1. When this cell is changed, the web query updates. Repeat the process, copying data from worksheet2, cell A2, to worksheet1, A1... Any help you can provide would be appreciated. Donnie |
| 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 | |||
Linear Mode
