• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and paste cells

aleksandra123

New Member
Hello Guys,

I am struggling a little bit with the macro to copy and paste data. So I have a table. I need to copy let's say cell A1 and past it into the cell A1 in another file but then by looping come back to the table copy cell A2 and paste to the cell A3. So repeat the same action untill the end of the table. Data has to be pasted to every second row.

Can you help me out?

Thanks!
 
Code:
Option Explicit

Sub cpypstalt()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lr As Long, lr2 As Long
    Dim i As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = 1
    Application.ScreenUpdating = False
    For i = 1 To lr
        s2.Range("A" & lr2) = Range("A" & i)
        lr2 = lr2 + 2
    Next i
    Application.ScreenUpdating = True
End Sub
 
Hi thanks for answer:)
I am trying to readjust the code to my needs but it seems that it doesn't work but I don't get any error.

I need to copy data from one workbook to another. After a small lifting the code looks like as follows

Sub cpypstalt()
Dim s1 As Workbook, s2 As Workbook
Set s1 = Workbooks("Book1.xlsm")
Set s2 = Workbooks("Book2.xlsm")
Dim lr As Long, lr2 As Long
Dim i As Long
lr = s1.Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
lr2 = 1
Application.ScreenUpdating = False
For i = 1 To lr
s2.Worksheets("Sheet1").Range("A" & lr2) = Range("A" & i)
lr2 = lr2 + 2
Next i
Application.ScreenUpdating = True
End Sub

Any ideas?
 
change this line of code
Code:
s2.Worksheets("Sheet1").Range("A" & lr2) = Range("A" & i)
to
Code:
s2.Worksheets("Sheet1").Range("A" & lr2) = s1.Worksheets("Sheet1"). Range("A" & i)

I suspect that the reference to the source was not clear in my code and therefore not clear in your code.
 
Hi thank for reply !!!!

I resolved the issue on myself but I 've come accross the next difficulties. I want to only copy filtered values from the column but not to copy the header of the column but start from the next cell under the column. It will be always change as I filter the column.

My code is now like here

Sub cpypstalt()
Dim s1 As Workbook, s2 As Workbook
Set s1 = Workbooks("Book1.xlsm")
Set s2 = Workbooks("Book2.xlsm")
Dim lr As Long, lr2 As Long
Dim i As Long
lr = s1.Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row
lr2 = 10
Application.ScreenUpdating = False
For i = 6 To lr
s2.Sheets("Sheet2").Range("K" & lr2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False = s1.Sheets("Sheet1").Range("H" & i).Copy
lr2 = lr2 + 2
Next i
Application.ScreenUpdating = True
End Sub

Any ideas?

Thanks!!!!!
 
Not sure what issues your are encountering but I noticed that you have two lines of code reversed and an extra = sign mixed in.
Code:
s1.Sheets("Sheet1").Range("H" & i).Copy
s2.Sheets("Sheet2").Range("K" & lr2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
It appears you are trying to paste before you copy. By the way, please use code tags when posting code. It helps the reader to see the code in the format that is representative in your worksheet and is easier to read and understand.
 
Last edited:
A few things I would comment on.
1. If you used tables (list objects in VBA) you would not need to depend upon direct cell references and End(xlUp) to determine the final row.
2. You are using copy/paste. Is it important to you that you transfer styles and fonts etc. or would the values do?
3. If you need only the values, you could read the entire range within the table into a variant array rather than looping cell by cell.
4. The data transfer from single to double spaced variables could be done in the code without any interaction with the second workbook.
5. The next step depends upon the content of the alternate rows. If data exists in them which needs to be preserved it too would need to be read so that it could be merged. If they are blank, the VBA array is simply written as a single block.

This is probably not something you would wish to investigate in the context of a production workbook but I believe it is something worth exploring as it can be an order of magnitude faster.
 
Back
Top