One of the files i frequently receive has to be modified in a slightly
different format. I recorded a macro for that and when i get another
similar file with the same number of rows (or records) there is no
problem in changing the data into the desired format. The problem arise
when the numbers of rows are different (and this is true in most cases).
Executing the macro will end in a mess of data. Can someone help me in
writing a macro that is valid no matter how many rows are included?

I attached an example file for this problem. The first table has to be
modified into the second one. My macro is helpful only in cases of 5
products, but i am looking for a macro that is valid for all cases (3,
5, 7, etc. records).
The number of months remains always the same.


+-------------------------------------------------------------------+
|Filename: Change.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4972 |
+-------------------------------------------------------------------+

--
keesberbee
------------------------------------------------------------------------
keesberbee's Profile: http://www.excelforum.com/member.php...o&userid=35899
View this thread: http://www.excelforum.com/showthread...hreadid=557789

keesberbee wrote:
One of the files i frequently receive has to be modified in a slightly
different format. I recorded a macro for that and when i get another
similar file with the same number of rows (or records) there is no
problem in changing the data into the desired format. The problem
arise when the numbers of rows are different (and this is true in
most cases). Executing the macro will end in a mess of data. Can
someone help me in writing a macro that is valid no matter how many
rows are included?

I attached an example file for this problem. The first table has to be
modified into the second one. My macro is helpful only in cases of 5
products, but i am looking for a macro that is valid for all cases (3,
5, 7, etc. records).
The number of months remains always the same.


+-------------------------------------------------------------------+
Filename: Change.doc |
Download: http://www.excelforum.com/attachment.php?postid=4972 |
+-------------------------------------------------------------------+


I think you should post your macro, so we could suggest where to improve...

--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Dear Franz,
Thanks for your reply. Hereby i attach the macro. As the macro shows it
includes alot of copy and paste codes. I suggest that experienced VBA
users know better and faster ways to execute this task.


+-------------------------------------------------------------------+
|Filename: Macro.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4973 |
+-------------------------------------------------------------------+

--
keesberbee
------------------------------------------------------------------------
keesberbee's Profile: http://www.excelforum.com/member.php...o&userid=35899
View this thread: http://www.excelforum.com/showthread...hreadid=557789

keesberbee wrote:
Dear Franz,
Thanks for your reply. Hereby i attach the macro. As the macro shows
it includes alot of copy and paste codes. I suggest that experienced
VBA users know better and faster ways to execute this task.


+-------------------------------------------------------------------+
Filename: Macro.doc |
Download: http://www.excelforum.com/attachment.php?postid=4973 |
+-------------------------------------------------------------------+

Hi,

Here is the modified macro:

Sub MoveData()
'
'
'
'
' Keyboard Shortcut: Ctrl+k
'
Dim lastrow As Long, newlr As Long

lastrow = Range("A65536").End(xlUp).Row


Range("C2:C" & lastrow).Copy Range("B" & lastrow).Offset(1, 0)
Range("C2:C" & lastrow).Clear
Range("D2:D" & lastrow).Copy Range("B" & 2 * lastrow)
Range("D2:D" & lastrow).Clear
Range("A2:A" & lastrow).Copy Range("A" & lastrow).Offset(1, 0)
Range("A2:A" & lastrow).Copy Range("A" & 2 * lastrow)
Application.CutCopyMode = False
Columns("A:A").Insert Shift:=xlToRight
Range("C1").Copy Range("A2:A" & lastrow)
Range("C1").Clear
Range("D1").Copy Range("A" & lastrow + 1 & ":A" & 2 * lastrow - 1)
Range("D1").Clear
Range("E1").Copy Range("A" & 2 * lastrow & ":A" & 3 * lastrow - 2)
Range("E1").Clear
Range("A1").Value = "Month"
Range("C1").Value = "Rev"
With Range("A1:C1")
.HorizontalAlignment = xlCenter
.Font.Bold = True
End With
Range("A1").Select

End Sub




--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy