Coding issue with Excel 2003
 

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

Coding issue with Excel 2003
 

Where do you set rngTemp? I see where you are trying to set the values in the
external sheet to rngTemp values but you never specify what rngTemp is.
--
HTH...

Jim Thomlinson


"Neil Holden" wrote:

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