N
Neil Holden
Hi all Gurus, below is code to transfer data from sheet 1 and 2 into an
external sheet. I want to have a button so that when pressed it transfers a
range of cells from sheet 1 and 2 and places it in the first available row in
the external sheet, all the information needs to go into sheet 1 of the
external sheet.
Please help, i would greatly appreciate it.
Neil.
Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet
Dim wb1 As Workbook, wb2 As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\sguk-app1\Business Objects\Expenses\test
data.xls")
wb1.Sheets("Sheet1").Range("A21:S81").Copy
wb2.Sheets("Sheet1").Range("A21:S81")
wb1.Sheets("Sheet2").Range("A21:S81").Copy
wb2.Sheets("Sheet2").Range("A21:S81")
wb2.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set wbBook = Workbooks.Open("\\sguk-app1\Business Objects\Expenses\test
data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
With rngTemp
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1
wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value
End With
wbBook.Close True
external sheet. I want to have a button so that when pressed it transfers a
range of cells from sheet 1 and 2 and places it in the first available row in
the external sheet, all the information needs to go into sheet 1 of the
external sheet.
Please help, i would greatly appreciate it.
Neil.
Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet
Dim wb1 As Workbook, wb2 As Workbook
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\sguk-app1\Business Objects\Expenses\test
data.xls")
wb1.Sheets("Sheet1").Range("A21:S81").Copy
wb2.Sheets("Sheet1").Range("A21:S81")
wb1.Sheets("Sheet2").Range("A21:S81").Copy
wb2.Sheets("Sheet2").Range("A21:S81")
wb2.Close True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Set wbBook = Workbooks.Open("\\sguk-app1\Business Objects\Expenses\test
data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
With rngTemp
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1
wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value
End With
wbBook.Close True