Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating Reformatting Macro
Project just got tossed to me with little or no IT backup. Need to
write a macro that will take data from columns "A" and "B" (3000+ rows worth), 52 rows at a time and paste it into rows "D" and "E" leaving "C" empty. This needs to be repeated so that the next 52 rows of data skip "F: and get pasted into "G" and "H". The values in A1:B1 also need to be placed in cells at the top of these pasted columns. Lastly, I need it to know when to stop automatically. If someone could point me towards examples that do any or all parts of this, I would greatly appreciate it. THANKS! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating Reformatting Macro
I wish I could say it looks good, but I don't understand much. I do
know however that it is not working. I get an error message: "Compile Error - Expected End Sub" Let me know what I am missing. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating Reformatting Macro
Give this a try. Make sure the constants are at the very top of the code
module. It is the exact same code just re-arranged a bit. It compiles at this end so it should be ok... Private Const IntRows As Integer = 52 'Must be at top of code window Private Const intColumns As Integer = 3 'Must be at top of code window Sub MoveRows() Dim wksFrom As Worksheet Dim wksTo As Worksheet Dim rngFrom As Range Dim rngToData As Range Dim rngToTitles As Range Dim rngTitles As Range Dim rngLastItem As Range Set wksFrom = Sheets("Sheet2") 'Change The Sheet Name Set wksTo = Worksheets.Add Set rngTitles = wksFrom.Range("A1:B1") Set rngToTitles = wksTo.Range("A1") Set rngLastItem = wksFrom.Range("A65536").End(xlUp) Set rngFrom = wksFrom.Range(wksFrom.Cells(2, 1), _ wksFrom.Cells(IntRows + 1, 2)) Set rngToData = wksTo.Range("A2") Do While Intersect(rngFrom, rngLastItem) Is Nothing rngFrom.Copy rngToData rngTitles.Copy rngToTitles Set rngFrom = rngFrom.Offset(IntRows, 0) Set rngToData = rngToData.Offset(IntRows, intColumns) Set rngToTitles = rngToTitles.Offset(0, intColumns) Loop End Sub -- HTH... Jim Thomlinson "New2VBA" wrote: I wish I could say it looks good, but I don't understand much. I do know however that it is not working. I get an error message: "Compile Error - Expected End Sub" Let me know what I am missing. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating Reformatting Macro
Still not working. Same error message.
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating Reformatting Macro
It compiles at my end. give this a try. Open a brand new workbook and paste
the code into one of the sheets or into a module and select Debug - Compile VBA Project -- HTH... Jim Thomlinson "New2VBA" wrote: Still not working. Same error message. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating Reformatting Macro
I trust things are working now?
-- HTH... Jim Thomlinson "New2VBA" wrote: Still not working. Same error message. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating Reformatting Macro
Hi Jim,
Thanks for all your help, but I am still getting an error message. The following line of code seems to halt the execution. Set rngLastItem = wksFrom.Range("A3274").End(xl*Up) Any help you can offer would be great. Thanks! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repeating Reformatting Macro
Ok, I got that line fixed. All my fault (copied with an error). Works
great with one small issue. I need all the columns to start at the 2nd row. This has done a great job of shifting them to the right, but doesn't bring them to the top. Thanks, Angela |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
repeating macro | Excel Discussion (Misc queries) | |||
repeating a macro | Excel Discussion (Misc queries) | |||
Macro for reformatting cells | Excel Worksheet Functions | |||
Repeating Macro | Excel Discussion (Misc queries) | |||
Repeating macro | Excel Programming |