Copy & Paste whole line automatically with VBA (excel)

Question

Sheets("Source").Select
Rows("1:1").Select
Selection.Copy
Sheets("Print").Select
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Sheets("Source").Select
Rows("2:2").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Print").Select
Rows("1:1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

So, this is the sample code. What I want to do is Copy each line from Source Sheet and Paste to 1:1 rows in Print Sheet automatically.

The range is different every time. It'll be great if the code works as rows number.

[2nd question] Alright, Now I got new problem. After I execute the code, Excel freeze.

here's the new code.

Dim i As Long 'i - Number of rows in Source list
NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count
For i = 1 To NumRows
    Worksheets("Source").Rows(i).Copy
    Worksheets("Print").Rows("1:1").PasteSpecial Paste:=xlPasteValues
    Worksheets("Print").PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
Next

Have a look [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1) on how to avoid using `.Select`. Also your question sounds to me like you want to look into using a `For` loop running down your dynamic range, or just copy past your dynamic range at once. — JvdV, Jan 25 '19 at 13:56

score 0 · Answer 1 · answered Jan 25 '19 at 13:58

0

You can use a FOR loop sequence but i'm not sure of what you want with the program. Can you please share some more details about it?

answered Jan 25 '19 at 13:58

Peter

16
4

1

Please don't post as an answer, this is more of a comment. – SJR Jan 25 '19 at 14:07
thats what i want but i need 50 reputation to being able to comment – Peter Jan 25 '19 at 14:09
1

OK, I'd forgotten the limit. However I think the idea is that you post answers to get your rep up to 50. – SJR Jan 25 '19 at 14:10
This should be changed like you can comment but need reputation for answers thats sounds more logical for me – Peter Jan 25 '19 at 14:12
Ha yes I agree, I don't know why it's that way. – SJR Jan 25 '19 at 14:14
"Oh so you asks good qeustions and got knowledge about something.. well we trust your answers!" aaaahhhhh "Oh so you know something, now you can ask about the problem itself!" *insert that silly meme here* – Peter Jan 25 '19 at 14:16
It seems that comments are regarded as a secondary activity so better to answer and edit questions to get the rep. Comments are regularly deleted anyway, so this exchange may well hit the dust soon. – SJR Jan 25 '19 at 14:22

Alexey C · Answer 2 · 2019-01-25T14:10:25.553

0

If i understand you correct, you must use construction like this:

Dim i as long 'i - Number of rows in Source list
i = 100 'for example
Worksheets("Source").Rows("1:" & i).Copy 
Worksheets("Print").Rows("1:1").PasteSpecial Paste:=xlPasteValues

edited Jan 25 '19 at 14:10

answered Jan 25 '19 at 14:05

Alexey C

172
6

score 0 · Answer 3 · answered Jan 25 '19 at 14:32

0

Let us assume that Source sheet structure like the image below:

and Print sheet is empty.

you could try:

Option Explicit

Sub test()

    Dim wsSource As Worksheet, wsPrint As Worksheet
    Dim rngCopy As Range
    Dim LastRow As Long, LastColumn As Long

    Set wsSource = ThisWorkbook.Worksheets("Source")
    Set wsPrint = ThisWorkbook.Worksheets("Print")

    With wsSource

        'Find last row of column A
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'Find last column or row 1
        LastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
        'Set range to copy
        Set rngCopy = .Range(.Cells(2, 1), .Cells(LastRow, LastColumn))

    End With

    rngCopy.Copy wsPrint.Range("A1")

End Sub

Result:

answered Jan 25 '19 at 14:32

Error 1004

7,877
3
23
46

Oh, I think there was some mistake with my explanation. I want to copy each row from Source sheet to 1:1 row only in Print sheet. – Darkohlins Jan 26 '19 at 10:12
The reason I want this, I'm gonna paste each line and Print, paste next line and Print.. like this. – Darkohlins Jan 26 '19 at 10:13
So there should be only 1 row in Print Sheet always. Just every different data from Source Sheet. – Darkohlins Jan 26 '19 at 10:15

Copy & Paste whole line automatically with VBA (excel)

3 Answers3