Need Help with Code - Copy & Paste

  • Thread starter Thread starter JStone0218
  • Start date Start date
J

JStone0218

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
 
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,

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 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
 
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
 
The problem with this solution is that the range changes from OP

And if it contained 20 this time and 200 last time then you will
have 180 extra values in Sheet1.

Chrissy.
 
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?
Click to expand...

There will always be a value...
 
JStone0218 wrote
Worksheet1, A1 is the data input range for a dynamic web query.


There will always be a value...
Click to expand...

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.
 
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.
Click to expand...

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
 
JStone0218 said:
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.
Click to expand...

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.
 
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
 
Back
Top