MACRO HELP CONTINUED
 

Below is the code for when a button is pressed it copies the range A13:Q75
and pastes to an external excel sheet. The trouble i'm having now is if the
user presses the button again i need the information to continue from the end
of the external excel list and paste that in instead of overwriting the
current data.

Please help


Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A13:Q75")
Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
With rngTemp
wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

MACRO HELP CONTINUED
 

I replied to an earlier question - but obviously got it wrong. Sorry.

so do you just want the data appended to data thates there already?

change this line

wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value

to
wsDest.Range("A2").End(xlDown).Offset(1).Resize(.R ows.Count,
..Columns.Count).Value = .Value

this will find the first empty cell below A1 and copy the block of data

"Neil Holden" wrote:


Below is the code for when a button is pressed it copies the range A13:Q75
and pastes to an external excel sheet. The trouble i'm having now is if the
user presses the button again i need the information to continue from the end
of the external excel list and paste that in instead of overwriting the
current data.

Please help


Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A13:Q75")
Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
With rngTemp
wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

MACRO HELP CONTINUED
 

I have done that and comes back with an error: Application defined error.

Thanks anyhow.

"Patrick Molloy" wrote:

I replied to an earlier question - but obviously got it wrong. Sorry.

so do you just want the data appended to data thates there already?

change this line

wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value

to
wsDest.Range("A2").End(xlDown).Offset(1).Resize(.R ows.Count,
.Columns.Count).Value = .Value

this will find the first empty cell below A1 and copy the block of data

"Neil Holden" wrote:


Below is the code for when a button is pressed it copies the range A13:Q75
and pastes to an external excel sheet. The trouble i'm having now is if the
user presses the button again i need the information to continue from the end
of the external excel list and paste that in instead of overwriting the
current data.

Please help


Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A13:Q75")
Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
With rngTemp
wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

MACRO HELP CONTINUED
 

the error occurs if there's currently nothing in the destination sheet. so
switch End(xlDown) to XLUP

change this
wsDest.Range("A2").End(xlDown).Offset(1).Resize(.R ows.Count,
..Columns.Count).Value = .Value

to

wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(.Rows.Count ,
..Columns.Count).Value = .Value



"Neil Holden" wrote:

I have done that and comes back with an error: Application defined error.

Thanks anyhow.

"Patrick Molloy" wrote:

I replied to an earlier question - but obviously got it wrong. Sorry.

so do you just want the data appended to data thates there already?

change this line

wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value

to
wsDest.Range("A2").End(xlDown).Offset(1).Resize(.R ows.Count,
.Columns.Count).Value = .Value

this will find the first empty cell below A1 and copy the block of data

"Neil Holden" wrote:


Below is the code for when a button is pressed it copies the range A13:Q75
and pastes to an external excel sheet. The trouble i'm having now is if the
user presses the button again i need the information to continue from the end
of the external excel list and paste that in instead of overwriting the
current data.

Please help


Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("A13:Q75")
Set wbBook = Workbooks.Open("C:\Documents and
Settings\neil.holden\Desktop\test2.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet
With rngTemp
wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value
lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1