VBA - Copy Several Ranges to last empty cell to the left

[OrochiNoob]

Hello awesome ppl

Hey I have an issue with my vba code that WORKS only on the 1st 5 rows, but fails when I go to low

This works

Sub Part_2()


Worksheets("Raw Data").Select
[B]Range("AL3:AL7").Select[/B]
Selection.Copy
[B]Range("D3:AJ3" & Columns.Count).End(xlToRight).Select[/B]
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues


End Sub

This DOES NOT

Worksheets("Raw Data").Select
[B]Range("AL13:A17").Select[/B]
Selection.Copy
[B]Range("D13:AJ13" & Columns.Count).End(xlToRight).Select[/B]
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues

For some reason when I do a range at D13:AJ17 it fails to either count or do the range
I have 8 Ranges that need to be copy pasted after a Query to fetch the data


Thank you for your time !!


Orochi

 

[Fluff]

Firstly is this a typo?

Range("AL13:A17").Select

or should it be AL17
Secondly can you please explain what you are trying to do.

 

[OrochiNoob]

@Fluff


Yes that is a typo for this ( I actually cheked my excel again and the typo is not there )

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]June[/TD]
[TD]06/01/2019[/TD]
[TD]06/02/2019[/TD]
[TD]06/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Source[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Field1[/TD]
[TD]800[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Field2[/TD]
[TD]500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]844[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Field3[/TD]
[TD]354[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Field3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Field4[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]64[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]06/01/2019[/TD]
[TD]06/02/2019[/TD]
[TD]06/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD]Source[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Field5[/TD]
[TD]588[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Field6[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD][/TD]
[TD]06/01/2019[/TD]
[TD]06/02/2019[/TD]
[TD]06/03/2019[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Field7[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So I'm copying the source
"G2:G6" to last empty column "C2:C6"
"G14:G15" to last empty column "C14:C15"
"G19" to last empty column "C19"

This will allow me to keep historical and forecast my information faster

( since this data from G column is copyied to the corresponding date 8 times )

Thank you
hope that make clearer the requirement

 

[Fluff]

Will the rows to be copied always be the same & do you need to copy each section at the same time?

 

[OrochiNoob]

The rows should not change and
Since the information is already available for copy then independant or individuial makes no difference

 

[Fluff]

Ok, how about

Sub OrochiNoob()
   Dim Ary As Variant
   Dim i As Long, Clmn As Long
   
   Ary = Array("AL3:AL7", "AL13:AL17", "AL19")
   With Sheets("Raw Data")
      Clmn = .Cells(2, 37).End(xlToLeft).Offset(, 1).Column
      For i = 0 To UBound(Ary)
         .Range(Ary(i)).Copy
         .Cells(.Range(Ary(i)).Row, Clmn).PasteSpecial xlPasteValues
      Next i
   End With
   Application.CutCopyMode = False
End Sub

Just add the rest of the range addresses to the array

 

[OrochiNoob]

It worked like a charm THANK YOU!!

Sub OrochiNoob()
   Dim Ary As Variant
   Dim i As Long, Clmn As Long
   
   Ary = Array("AL3:AL7", "AL13:AL17", "Al32:AL36", "AL42:AL60", "AL63:AL81", "AL84:AL102", "AL105:AL123", "AL126:AL144")
   With Sheets("Raw Data")
      Clmn = .Cells(6, 37).End(xlToLeft).Offset(, 1).Column
      For i = 0 To UBound(Ary)
         .Range(Ary(i)).Copy
         .Cells(.Range(Ary(i)).Row, Clmn).PasteSpecial xlPasteValues
      Next i
   End With
   Application.CutCopyMode = False
End Sub

 

[Fluff]

You're welcome & thanks for the feedback