PDA

View Full Version : [SOLVED] Copy and paste row of data to another worksheet macro



podder
12-31-2017, 03:44 PM
Hi,
I am trying to copy data from rows in sheet1 to new worksheets in the same workbook.
I have the worksheet names in column A. I would like a macro to copy data from the same row of the worksheet into that worksheet and continue down the column until a blank cell exists. The rows will have data in columns B to E. When pasting into the worksheet, I would like the data to be entered into cell D3 to G3.
Can anybody please help.

mana
12-31-2017, 06:55 PM
Option Explicit

Sub test()
Dim i As Long
Dim wsn As String

Do
i = i + 1
wsn = Cells(i, "a").Value
If wsn = "" Then Exit Do
If IsObject(Evaluate("'" & wsn & "'!a1")) Then
Worksheets(wsn).Cells(i, "d").Resize(, 4).Value = _
Cells(i, "b").Resize(, 4).Value
End If
Loop

End Sub



マナ

podder
12-31-2017, 10:33 PM
Option Explicit

Sub test()
Dim i As Long
Dim wsn As String

Do
i = i + 1
wsn = Cells(i, "a").Value
If wsn = "" Then Exit Do
If IsObject(Evaluate("'" & wsn & "'!a1")) Then
Worksheets(wsn).Cells(i, "d").Resize(, 4).Value = _
Cells(i, "b").Resize(, 4).Value
End If
Loop

End Sub



マナ
Thanks for the reply.
However, the data is relevant to the position it is in on sheet1. Is it possible to have the data placed at D3 to G3 each time.
Thanks again

mana
12-31-2017, 10:58 PM
Worksheets(wsn).Cells("D3").Resize(, 4).Value = _
Cells(i, "b").Resize(, 4).Value

podder
12-31-2017, 11:20 PM
Worksheets(wsn).Cells("D3").Resize(, 4).Value = _
Cells(i, "b").Resize(, 4).Value

Mana
Thanks for your help its much appreciated.
I am still having issues, I have replaced the new lines but now get an Runtime error 1004.
Am I doing something wrong??

mana
12-31-2017, 11:29 PM
Sorry

Worksheets(wsn).Cells(3, "D").Resize(, 4).Value = _
Cells(i, "b").Resize(, 4).Value

podder
12-31-2017, 11:37 PM
Sorry

Worksheets(wsn).Cells(3, "D").Resize(, 4).Value = _
Cells(i, "b").Resize(, 4).Value

Works perfectly thanks
Much appreciated