Hello,
I am trying to have the macro loop through the worksheets, copy data, paste to another tab, under the last set of data each time. The issue is that when I use the below, the column of data that was originally pasted in does not go all the way to the bottom of the rows where there is data in other columns.
.Range("D" & Rows.Count).End(xlUp).Offset(1)For example in the picture you can see there are blanks in column D but the data for the Quarters to the right are populated. (These Quarter columns are not continuous data either/have blanks so using the above code in a similar fashion won't work I don't think?)
[ATTACH=CONFIG]57541[/ATTACH]
The only way I can think of is to run through the first work sheet as listed below, then the other worksheets with something like:
Worksheets("Sup_Data").Range("D" & intgroup).Offset(1).PasteSpecial (xlPasteValues)
But this only results in pasting over the same cells each time. I'd need each subsequent sheet to be intgroup *2 or something?
Dim intCounter, intIndex, intConvert As Integer
Const intRow_01 As Integer = 21
Const intRow_02 As Integer = 2
Const intCol_01 As Integer = 1
Const intCol_02 As Integer = 2
Const intCol_03 As Integer = 3
Const intCol_07 As Integer = 7
Const intCol_09 As Integer = 9
Const intCol_12 As Integer = 12
Const intCol_15 As Integer = 15
Const intCol_23 As Integer = 23
Const intCol_26 As Integer = 26
Const intCol_29 As Integer = 29
Const intCol_32 As Integer = 32
Const CMTRow As Integer = 588
Const CSGRows As Integer = 606
Public Const intGroup As Integer = 3612
Public Const intRow_68 As Integer = 68
Dim strNew, strOld As String
Dim x As Variant
Dim y As Variant
'Copy and paste Supervised data into Sup_Data Tab
y = Array("CAN", "USA", "ASG", "Gallia", "IGEM", "LAT", "NOR", "SPAI", "UKI", "ANZ", "ASE", "China", "IND", "JPN", "Skorea")
For Each x In y
With Worksheets(x)
.Range(.Cells(intRow_01, intCol_01), .Cells((intRow_01 + intGroup), intCol_07)).Copy
Worksheets("Sup_Data").Range("D" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
.Range(.Cells(intRow_01, intCol_09), .Cells((intRow_01 + intGroup), intCol_09)).Copy
Worksheets("Sup_Data").Range("K" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
.Range(.Cells(intRow_01, intCol_12), .Cells((intRow_01 + intGroup), intCol_12)).Copy
Worksheets("Sup_Data").Range("L" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
.Range(.Cells(intRow_01, intCol_15), .Cells((intRow_01 + intGroup), intCol_15)).Copy
Worksheets("Sup_Data").Range("M" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
.Range(.Cells(intRow_01, intCol_23), .Cells((intRow_01 + intGroup), intCol_23)).Copy
Worksheets("Sup_Data").Range("N" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
.Range(.Cells(intRow_01, intCol_26), .Cells((intRow_01 + intGroup), intCol_26)).Copy
Worksheets("Sup_Data").Range("O" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
.Range(.Cells(intRow_01, intCol_29), .Cells((intRow_01 + intGroup), intCol_29)).Copy
Worksheets("Sup_Data").Range("P" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
.Range(.Cells(intRow_01, intCol_32), .Cells((intRow_01 + intGroup), intCol_32)).Copy
Worksheets("Sup_Data").Range("Q" & Rows.Count).End(xlUp).Offset(1).PasteSpecial (xlPasteValues)
End With
NextAny help would be greatly appreciated!
Thank you!
