Specific Row Designation
I have a macro which copies specific information from a target row on one
worksheet, and copies that information to the first blank cell it finds on a designated worksheet. However, I need for it to begin looking for the first blank cell on row 12. Here is the macro I currently have: Sub CopyRetailerInfo() Dim BlankCell As Integer Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy Worksheets("Field Activity Report").Select BlankCell = Application.WorksheetFunction.CountA(Worksheets("F ield Activity Report").Range("D:D")) + 1 Worksheets("Field Activity Report").Cells(BlankCell, 4).Select Selection.PasteSpecial (xlPasteValues) Application.CutCopyMode = False Worksheets("Survey Spreadsheet").Select End Sub Can you help correct this macro so it begins on row 12? Thanks for your help. |
Specific Row Designation
One way:
Public Sub CopyRetailerInfo() Dim rDest As Range With Worksheets("Field Activity Report") Set rDest = .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) If rDest.Row < 12 Then Set rDest = .Range("D12") End With rDest.Resize(1, 5).Value = ActiveCell.Resize(1, 5).Value End Sub Note that selection/activation is almost never needed. Using the range objects directly is faster, generates smaller code, and IMO, is easier to maintain. In article , "Larry" wrote: I have a macro which copies specific information from a target row on one worksheet, and copies that information to the first blank cell it finds on a designated worksheet. However, I need for it to begin looking for the first blank cell on row 12. Here is the macro I currently have: Sub CopyRetailerInfo() Dim BlankCell As Integer Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy Worksheets("Field Activity Report").Select BlankCell = Application.WorksheetFunction.CountA(Worksheets("F ield Activity Report").Range("D:D")) + 1 Worksheets("Field Activity Report").Cells(BlankCell, 4).Select Selection.PasteSpecial (xlPasteValues) Application.CutCopyMode = False Worksheets("Survey Spreadsheet").Select End Sub Can you help correct this macro so it begins on row 12? Thanks for your help. |
Specific Row Designation
Thanks, that does work and your information was exactly right. I found that
changing Range("D:D")) + 1 to Range("D:D")) +10 moved the information down to the row I was shooting for. Thanks for all your help! "JE McGimpsey" wrote: One way: Public Sub CopyRetailerInfo() Dim rDest As Range With Worksheets("Field Activity Report") Set rDest = .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) If rDest.Row < 12 Then Set rDest = .Range("D12") End With rDest.Resize(1, 5).Value = ActiveCell.Resize(1, 5).Value End Sub Note that selection/activation is almost never needed. Using the range objects directly is faster, generates smaller code, and IMO, is easier to maintain. In article , "Larry" wrote: I have a macro which copies specific information from a target row on one worksheet, and copies that information to the first blank cell it finds on a designated worksheet. However, I need for it to begin looking for the first blank cell on row 12. Here is the macro I currently have: Sub CopyRetailerInfo() Dim BlankCell As Integer Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy Worksheets("Field Activity Report").Select BlankCell = Application.WorksheetFunction.CountA(Worksheets("F ield Activity Report").Range("D:D")) + 1 Worksheets("Field Activity Report").Cells(BlankCell, 4).Select Selection.PasteSpecial (xlPasteValues) Application.CutCopyMode = False Worksheets("Survey Spreadsheet").Select End Sub Can you help correct this macro so it begins on row 12? Thanks for your help. |
All times are GMT +1. The time now is 09:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com