My Data Table

[EMoe]

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

[Rowan]

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
>
>

[EMoe]

Thanks very much,

This code works perfectly.

Regards,
EMoe

[EMoe]

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

[Norman Jones]

Hi Emoe,

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

What error do you get?

---
Regards,
Norman



"EMoe" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[EMoe]

Thanks Norman; it works now.

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

Thanks Again,
Emoe