Using Macro to Copy/Paste
Hi,
I need help with a Macro on Excel. I have a large data set. And I want to move the numbers up to Bangor and delete the space between the two. The moves the next set of numbers up to Baton Rouge, and delete the space after that. (if that makes any sense?). When I record the macro and replay, it just keeps going to the original cell. Thanks a lot! BANGOR ME . . . . . . . . . 54 54 45 45 BATON ROUGE LA. . . . . . . 65 56 56 87 |
Using Macro to Copy/Paste
Post your macro code, and we can modify.
-- ** John C ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "LucyN" wrote: Hi, I need help with a Macro on Excel. I have a large data set. And I want to move the numbers up to Bangor and delete the space between the two. The moves the next set of numbers up to Baton Rouge, and delete the space after that. (if that makes any sense?). When I record the macro and replay, it just keeps going to the original cell. Thanks a lot! BANGOR ME . . . . . . . . . 54 54 45 45 BATON ROUGE LA. . . . . . . 65 56 56 87 |
Using Macro to Copy/Paste
I'm not sure if that helps, first time using macros. Thanks for all the help!
Keyboard Shortcut: Ctrl+b Range("B17:G17").Select Selection.Copy Range("B16").Select ActiveSheet.Paste Rows("17:17").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Range("B18:G18").Select Selection.Copy Range("B17").Select ActiveSheet.Paste Rows("18:18").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Range("B19:G19").Select Selection.Copy Range("B18").Select ActiveSheet.Paste Rows("19:19").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp End Sub |
Using Macro to Copy/Paste
Option Explicit
Sub testme01() Dim wks As Worksheet Dim LastRow As Long Dim FirstRow As Long Dim iRow As Long Set wks = Worksheets("Sheet1") With wks FirstRow = 1 'no headers 'lastrow is one more than the last row in column A LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 For iRow = LastRow To FirstRow + 1 Step -2 'b:g is 6 columns, so .resize(1,6) .Cells(iRow, "B").Resize(1, 6).Copy _ Destination:=.Cells(iRow - 1, "b") .Rows(iRow).Delete Next iRow End With End Sub LucyN wrote: Hi, I need help with a Macro on Excel. I have a large data set. And I want to move the numbers up to Bangor and delete the space between the two. The moves the next set of numbers up to Baton Rouge, and delete the space after that. (if that makes any sense?). When I record the macro and replay, it just keeps going to the original cell. Thanks a lot! BANGOR ME . . . . . . . . . 54 54 45 45 BATON ROUGE LA. . . . . . . 65 56 56 87 -- Dave Peterson |
Using Macro to Copy/Paste
Hi Lucy
Replace your macro with the following code Sub Copydata() Dim lr As Long, lc As Long, i As Long lr = ActiveSheet.UsedRange.Rows.Count lc = ActiveSheet.UsedRange.Columns.Count For i = lr To 2 Step -2 Range(Cells(i, 2), Cells(i, lc)).Copy Cells(i - 1, 2) Rows(i).Delete Next i End Sub -- Regards Roger Govier "LucyN" wrote in message ... I'm not sure if that helps, first time using macros. Thanks for all the help! Keyboard Shortcut: Ctrl+b Range("B17:G17").Select Selection.Copy Range("B16").Select ActiveSheet.Paste Rows("17:17").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Range("B18:G18").Select Selection.Copy Range("B17").Select ActiveSheet.Paste Rows("18:18").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp Range("B19:G19").Select Selection.Copy Range("B18").Select ActiveSheet.Paste Rows("19:19").Select Application.CutCopyMode = False Selection.Delete Shift:=xlUp End Sub |
Using Macro to Copy/Paste
Thank you all! =) |
All times are GMT +1. The time now is 02:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com