Copy-Paste Issue

[Boris7265]

Hello to all of you.
Here is a problem that I am stuck with on my job. I run every day report from another accounting system in excel format and data on worksheet located like this: one Set of datas are in column A-J of row 1 , and second set of dates are in the columns L to U in row 1 as well .Then in row 2 the same ne set of datas are in the columns A to J and another are in the columns L to U . I am trying to move datas that are in columns L to U to at beginning worksheet to columns A to J . First , I made a macro to insert new blank rows until end of the datas of columns A
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Range("A1").Select<o></o>
Selection.End(xlDown).Select<o></o>
Do Until ActiveCell.Row = 1<o></o>
ActiveCell.EntireRow.Insert Shift:=xlDown<o></o>
<o> </o>
ActiveCell.Offset(-1, 0).Select<o></o>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Loop</st1lace>
<o> </o>
The macro is working , now I need to copy-paste columns L to U onto blank rows that were just entered between rows in columns A to J and delete the datas in columns L to U .The worksheet contains 1000-1500 rows averagely so that copy-paste them manually would be hard . I am green in vb specially excel so any help would ne very helpful and appreciated.
<o> </o>
Thank you in advance,
<o> </o>
The Best Regards,
<o> </o>
BorisGomel

 

[Rick Rothstein]

I think this macro will do what you want faster than the approach you were trying initially...

Sub BlendAtoJwithLtoU()
  Dim AJ As Variant, LU As Variant, AJLU As Variant, LastRow As Long, Rw As Long, Cl As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  AJ = Range("A1:J" & LastRow)
  LU = Range("L1:U" & LastRow)
  ReDim AJLU(1 To 2 * LastRow, 1 To 10)
  For Rw = 1 To LastRow
    For Cl = 1 To 10
      AJLU(2 * Rw - 1, Cl) = AJ(Rw, Cl)
      AJLU(2 * Rw, Cl) = LU(Rw, Cl)
    Next
  Next
  Application.ScreenUpdating = False
  Range("A1:J" & 2 * LastRow) = AJLU
  Columns("L:U").Clear
  Application.ScreenUpdating = True
End Sub

 

[Boris7265]

Thank you very much sir!
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I have tried your macro and it is really amazing. A thousand thanks.
<o> </o>
Sir, if I may just one small issue to address here. The datas are joined nicely and between every two rows there are two blanks rows. The problem that I need to delete the two blanks rows that datas should have been in each row. Really appreciate for helping me with it .

BorisGomel

 

[Rick Rothstein]

The datas are joined nicely and between every two rows there are two blanks rows. The problem that I need to delete the two blanks rows that datas should have been in each row. Really appreciate for helping me with it.l

Did you merge your posted code with my code? I ask because my code was meant to be the complete code, fully replacing the code you posted. If you just run the code I posted, as is, no blank lines are introduced. At least that is how it works on my sample data.

 

[Boris7265]

Got it!!!!
My opps.
Thank you very much Sir!!!
You really easy my job and my deepest regards and appreciation to you!!!

BorisGomel