| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy/paste macro blocking
Dear All,
I prepared a code that should: - copy last used line from currently used workbook - paste it to the first free line on all other opened workbooks For some reason, the code blocks on the line: FirstFreeRow = Range("B7").End(xlDown + 1).Row Can you tell me what should I modify? Thanks a lot, Mark ----------------- Here's the code: Sub Macro7() Dim wbk As Workbook lastDataRow = Range("B7").End(xlDown).Row Range("B" & lastDataRow).Select Range(ActiveCell, ActiveCell.Offset(0, 9)).Select Selection.Copy 'it would be also useful to exclude the file we just used, but at the end I can also live without it (=delete the double row manually after the macro is executed) For Each wbk In Application.Workbooks wbk.Activate Worksheets("Sheet1").Activate FirstFreeRow = Range("B7").End(xlDown + 1).Row Range("B" & FirstFreeRow).Select Selection.Paste Next wbk End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy/paste macro blocking
Your closing parenthesis in the wrong place.
FirstFreeRow = Range("B7").End(xlDown).Row+1 James markx wrote: Dear All, I prepared a code that should: - copy last used line from currently used workbook - paste it to the first free line on all other opened workbooks For some reason, the code blocks on the line: FirstFreeRow = Range("B7").End(xlDown + 1).Row Can you tell me what should I modify? Thanks a lot, Mark ----------------- Here's the code: Sub Macro7() Dim wbk As Workbook lastDataRow = Range("B7").End(xlDown).Row Range("B" & lastDataRow).Select Range(ActiveCell, ActiveCell.Offset(0, 9)).Select Selection.Copy 'it would be also useful to exclude the file we just used, but at the end I can also live without it (=delete the double row manually after the macro is executed) For Each wbk In Application.Workbooks wbk.Activate Worksheets("Sheet1").Activate FirstFreeRow = Range("B7").End(xlDown + 1).Row Range("B" & FirstFreeRow).Select Selection.Paste Next wbk End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy/paste macro blocking
Hi, thanks...
It's strange, because it only puts the problem to the next line of code... Code stops at: Range("B" & FirstFreeRow).Select I'll try to work on this, but if you have a clue what's going wrong, pls let know... Cheers, Mark "Zone" wrote in message ups.com... Your closing parenthesis in the wrong place. FirstFreeRow = Range("B7").End(xlDown).Row+1 James markx wrote: Dear All, I prepared a code that should: - copy last used line from currently used workbook - paste it to the first free line on all other opened workbooks For some reason, the code blocks on the line: FirstFreeRow = Range("B7").End(xlDown + 1).Row Can you tell me what should I modify? Thanks a lot, Mark ----------------- Here's the code: Sub Macro7() Dim wbk As Workbook lastDataRow = Range("B7").End(xlDown).Row Range("B" & lastDataRow).Select Range(ActiveCell, ActiveCell.Offset(0, 9)).Select Selection.Copy 'it would be also useful to exclude the file we just used, but at the end I can also live without it (=delete the double row manually after the macro is executed) For Each wbk In Application.Workbooks wbk.Activate Worksheets("Sheet1").Activate FirstFreeRow = Range("B7").End(xlDown + 1).Row Range("B" & FirstFreeRow).Select Selection.Paste Next wbk End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy/paste macro blocking
markx, Did you dim FirstFreeRow as Long? When the code chokes, go into
debug and rest the mouse over FirstFreeRow on the highlighted line. It should show the value of FirstFreeRow. What is it? James markx wrote: Hi, thanks... It's strange, because it only puts the problem to the next line of code... Code stops at: Range("B" & FirstFreeRow).Select I'll try to work on this, but if you have a clue what's going wrong, pls let know... Cheers, Mark "Zone" wrote in message ups.com... Your closing parenthesis in the wrong place. FirstFreeRow = Range("B7").End(xlDown).Row+1 James markx wrote: Dear All, I prepared a code that should: - copy last used line from currently used workbook - paste it to the first free line on all other opened workbooks For some reason, the code blocks on the line: FirstFreeRow = Range("B7").End(xlDown + 1).Row Can you tell me what should I modify? Thanks a lot, Mark ----------------- Here's the code: Sub Macro7() Dim wbk As Workbook lastDataRow = Range("B7").End(xlDown).Row Range("B" & lastDataRow).Select Range(ActiveCell, ActiveCell.Offset(0, 9)).Select Selection.Copy 'it would be also useful to exclude the file we just used, but at the end I can also live without it (=delete the double row manually after the macro is executed) For Each wbk In Application.Workbooks wbk.Activate Worksheets("Sheet1").Activate FirstFreeRow = Range("B7").End(xlDown + 1).Row Range("B" & FirstFreeRow).Select Selection.Paste Next wbk End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy/paste macro blocking
OK, I understand it better now - didn't know that you can actually see the
value of the variable... First Free Row was 65537 (on one of the worksheets, there was no record under B7, so it went to the very last row on the worksheet). So if I want to use this code, I should find some solution for this kind of situations... Were you already confronted with this? * * * By the way, I used another code, going from the bottom of the worksheet... it works! Only needed to change Selection.Paste to ActiveSheet.Paste (don't know exactly why...) --------------- Sub TheCodeThatWorks() Dim wbk As Workbook lastDataRow = Range("B7").End(xlDown).Row Range("B" & lastDataRow).Select Range(ActiveCell, ActiveCell.Offset(0, 9)).Select Selection.Copy For Each wbk In Application.Workbooks wbk.Activate Worksheets("Sheet1").Activate Lr = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("B" & Lr).Select ActiveSheet.Paste Next wbk End Sub "Zone" wrote in message ups.com... markx, Did you dim FirstFreeRow as Long? When the code chokes, go into debug and rest the mouse over FirstFreeRow on the highlighted line. It should show the value of FirstFreeRow. What is it? James markx wrote: Hi, thanks... It's strange, because it only puts the problem to the next line of code... Code stops at: Range("B" & FirstFreeRow).Select I'll try to work on this, but if you have a clue what's going wrong, pls let know... Cheers, Mark "Zone" wrote in message ups.com... Your closing parenthesis in the wrong place. FirstFreeRow = Range("B7").End(xlDown).Row+1 James markx wrote: Dear All, I prepared a code that should: - copy last used line from currently used workbook - paste it to the first free line on all other opened workbooks For some reason, the code blocks on the line: FirstFreeRow = Range("B7").End(xlDown + 1).Row Can you tell me what should I modify? Thanks a lot, Mark ----------------- Here's the code: Sub Macro7() Dim wbk As Workbook lastDataRow = Range("B7").End(xlDown).Row Range("B" & lastDataRow).Select Range(ActiveCell, ActiveCell.Offset(0, 9)).Select Selection.Copy 'it would be also useful to exclude the file we just used, but at the end I can also live without it (=delete the double row manually after the macro is executed) For Each wbk In Application.Workbooks wbk.Activate Worksheets("Sheet1").Activate FirstFreeRow = Range("B7").End(xlDown + 1).Row Range("B" & FirstFreeRow).Select Selection.Paste Next wbk End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy/paste macro blocking
I almost invariably go from the bottom up, so I don't run into this.
If you want to use xlDown, you could probably avoid the problem with something like If LastFreeRow65536 then LastFreeRow='something else! Cheers! James markx wrote: OK, I understand it better now - didn't know that you can actually see the value of the variable... First Free Row was 65537 (on one of the worksheets, there was no record under B7, so it went to the very last row on the worksheet). So if I want to use this code, I should find some solution for this kind of situations... Were you already confronted with this? * * * By the way, I used another code, going from the bottom of the worksheet... it works! Only needed to change Selection.Paste to ActiveSheet.Paste (don't know exactly why...) --------------- Sub TheCodeThatWorks() Dim wbk As Workbook lastDataRow = Range("B7").End(xlDown).Row Range("B" & lastDataRow).Select Range(ActiveCell, ActiveCell.Offset(0, 9)).Select Selection.Copy For Each wbk In Application.Workbooks wbk.Activate Worksheets("Sheet1").Activate Lr = Sheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("B" & Lr).Select ActiveSheet.Paste Next wbk End Sub "Zone" wrote in message ups.com... markx, Did you dim FirstFreeRow as Long? When the code chokes, go into debug and rest the mouse over FirstFreeRow on the highlighted line. It should show the value of FirstFreeRow. What is it? James markx wrote: Hi, thanks... It's strange, because it only puts the problem to the next line of code... Code stops at: Range("B" & FirstFreeRow).Select I'll try to work on this, but if you have a clue what's going wrong, pls let know... Cheers, Mark "Zone" wrote in message ups.com... Your closing parenthesis in the wrong place. FirstFreeRow = Range("B7").End(xlDown).Row+1 James markx wrote: Dear All, I prepared a code that should: - copy last used line from currently used workbook - paste it to the first free line on all other opened workbooks For some reason, the code blocks on the line: FirstFreeRow = Range("B7").End(xlDown + 1).Row Can you tell me what should I modify? Thanks a lot, Mark ----------------- Here's the code: Sub Macro7() Dim wbk As Workbook lastDataRow = Range("B7").End(xlDown).Row Range("B" & lastDataRow).Select Range(ActiveCell, ActiveCell.Offset(0, 9)).Select Selection.Copy 'it would be also useful to exclude the file we just used, but at the end I can also live without it (=delete the double row manually after the macro is executed) For Each wbk In Application.Workbooks wbk.Activate Worksheets("Sheet1").Activate FirstFreeRow = Range("B7").End(xlDown + 1).Row Range("B" & FirstFreeRow).Select Selection.Paste Next wbk End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
| Non-blocking calls to Excel Macro using OLE Automation | Excel Programming | |||
| Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| Macro to Copy/Paste then Paste to Next Line | Excel Programming | |||
Linear Mode
