Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Continued issues | Excel Programming | |||
Median continued | Excel Discussion (Misc queries) | |||
macro doesn't compile after module crashed continued from scary th | Excel Programming | |||
Macro To Change Cell Color (Continued) | Excel Worksheet Functions | |||
Continued Conversion of data from rows to columns in a macro. | Excel Programming |