My Data Table
 

Hello,

I'm looking for a code that will copy data from column B (say B5:B10)
on sheet1, workbook name DataInfo. Then open a workbook named DataTable
on c drive (c:\DataTable), then paste the values on sheet1, starting at
row 4 (say B4:G4 going across). With each click of the button the macro
need to paste down to the next row directly under the first set of
numbers; click again, next row.

I've seen macros that pastes from column to column. I don't know how to
go from one row to the next.

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=470659

My Data Table
 

Maybe like this:

Sub MvData()
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Dim DataBk As Workbook
Dim InfoBk As Workbook
Dim eRow As Long
Set InfoBk = Workbooks("DataInfo.xls")
Set DataBk = Workbooks.Open("C:\DataTable.xls")
eRow = DataBk.Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
If eRow < 4 Then
eRow = 4
Else
eRow = eRow + 1
End If
InfoBk.Sheets("Sheet1").Range("B5:B10").Copy
DataBk.Sheets("Sheet1").Cells(eRow, 2).PasteSpecial _
Transpose:=True
Application.CutCopyMode = False
DataBk.Close True
ErrorHandler:
Application.ScreenUpdating = True
End Sub

Hope this helps
Rowan

EMoe wrote:
Hello,

I'm looking for a code that will copy data from column B (say B5:B10)
on sheet1, workbook name DataInfo. Then open a workbook named DataTable
on c drive (c:\DataTable), then paste the values on sheet1, starting at
row 4 (say B4:G4 going across). With each click of the button the macro
need to paste down to the next row directly under the first set of
numbers; click again, next row.

I've seen macros that pastes from column to column. I don't know how to
go from one row to the next.

Thanks,
EMoe

My Data Table
 

Thanks very much,

This code works perfectly.

Regards,
EMo

--
EMo
-----------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318
View this thread: http://www.excelforum.com/showthread.php?threadid=47065

My Data Table
 

One more thing. With the code below, I need to do a paste special,
pasting only the values. I tried to add Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, after the pasteSpecial _ but got an error.

Right now the code works well, but it's copying and pasting the
formulas, and not the values

Sub MvData()
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Dim DataBk As Workbook
Dim InfoBk As Workbook
Dim eRow As Long
Set InfoBk = Workbooks("DataInfo.xls")
Set DataBk = Workbooks.Open("C:\DataTable.xls")
eRow = DataBk.Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
If eRow < 4 Then
eRow = 4
Else
eRow = eRow + 1
End If
InfoBk.Sheets("Sheet1").Range("B5:B10").Copy
DataBk.Sheets("Sheet1").Cells(eRow, 2).PasteSpecial _
Transpose:=True
Application.CutCopyMode = False
DataBk.Close True
ErrorHandler:
Application.ScreenUpdating = True
End Sub

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=470659

My Data Table
 

Hi Emoe,

DataBk.Sheets("Sheet1").Cells(eRow, 2).PasteSpecial _
Paste:=xlValues, _
Transpose:=True
worked for me.

What error do you get?

---
Regards,
Norman



"EMoe" wrote in message
...

One more thing. With the code below, I need to do a paste special,
pasting only the values. I tried to add Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, after the pasteSpecial _ but got an error.

Right now the code works well, but it's copying and pasting the
formulas, and not the values

Sub MvData()
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Dim DataBk As Workbook
Dim InfoBk As Workbook
Dim eRow As Long
Set InfoBk = Workbooks("DataInfo.xls")
Set DataBk = Workbooks.Open("C:\DataTable.xls")
eRow = DataBk.Sheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row
If eRow < 4 Then
eRow = 4
Else
eRow = eRow + 1
End If
InfoBk.Sheets("Sheet1").Range("B5:B10").Copy
DataBk.Sheets("Sheet1").Cells(eRow, 2).PasteSpecial _
Transpose:=True
Application.CutCopyMode = False
DataBk.Close True
ErrorHandler:
Application.ScreenUpdating = True
End Sub

Thanks,
EMoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile:
http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=470659

My Data Table
 

Thanks Norman; it works now.

I was doing PasteValues instead of just values like you have it.

Thanks Again,
Emoe


--
EMoe
------------------------------------------------------------------------
EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
View this thread: http://www.excelforum.com/showthread...hreadid=470659