Macro to take data from one worksheet to another but into rows

[RobSA]

Hi Folks,

My plan is to take data from various cells in a worksheet and transfer it to a different workbook and present this data in rows.

I have tried to do this in the following macro

Sub Macro2()
'
' Macro2 Macro
'

'
Dim itemProjectName As String
Dim itemScopeofWork As String

Dim myData As Workbook

Worksheets("Sheet1").Select
itemProject = Range("B6")

Set myData = Workbooks.Open("C:\Rob\Test.xlsm")

Worksheets("SheetA").Select
Worksheets("Sheet1").Range("B6").Select

RowCount = Worksheets("SheetA").Range("B6").CurrentRegion.Rows.Count
With Worksheets("SheetA").Range("B6")
Offset(RowCount, 0) = itemProjectName
Offset(RowCount, 1) = itemScopeofWork
End With
myData.Save

End Sub

 

[Deepak]

Are u looking for this.

Sub Macro2_Mod()

Dim myData As Workbook, RowCount As Long
Dim rng1 As Range, rng2 As Range

Set rng1 = Sheets("Sheet1").Range("B6")

Set myData = Workbooks.Open("C:\Users\Deepak\Desktop\Book1.xlsb")
Set rng2 = myData.Sheets("Sheet1").Range("B6")
RowCount = rng2.CurrentRegion.Rows.Count

rng2.Offset(RowCount, 0).Value = rng1.Value
rng2.Offset(RowCount, 1).Value = rng1.Offset(0, 1).Value

myData.Save
End Sub

 

[RobSA]

Thanks Deepak