Copy and combine dynamic ranges to new sheet Excel Macro

Question

I have been trying to figure this out for a while but can’t get it to work, since the macro code need to be a little more dynamic. I am new to writing macros and would really appreciate your help.

I have Sheet1 with two ranges:

• The first Range is from A2:MXXX (depending on how many rows I evaluate)

• The second range is A3001:M3XXX (also depending on how many rows I evaluate).

  1. I want to copy the first range until the last row in column A that contains a value to Sheet2.
  2. Then, I want to copy the second range until the last row in column A that contains a value and put it right after the first range in Sheet2.
  3. Then I want to sort the new combined range based on column B. (Ascending)
  4. Then if possible export the sorted and combined range to .csv

(Please note, that I only want to copy the values in each cell, not the formulas).

Many Thanks, Adam

Answer 1

Practice this 1st.

Select the 1st range to copy. For example select cells A2:A3000 Press F5 key and select Special cells Check off constants then OK, You will see that all the Non-Blank cells have been selected. Right click(or click the Copy Icon) on one of the select cells and select Copy, Goto sheet 2 and select A1 and Paste Special Values.

Select the next range of cells to copy and repeat until Paste, Once in sheet 2, goto special cells again and select last cell and OK Select on cell below that and paste.

Practice this until you get it right.

Once you have perfected those actions, use the Macro Recorder to make a macro.

Answer 2

If I correctly understand what you're looking for then this should do the trick. This doesn't do the .csv save for you. Let us know if you have trouble with that part.

Sub moveRows()

    Dim lastRow1 As Integer
    Dim lastRow2 As Integer
    Dim secondRangeSize As Integer

    With Sheets("Sheet1")
        lastRow1 = .Cells(3000, "A").End(xlUp).Row
        lastRow2 = .Cells(6000, "A").End(xlUp).Row
    End With

    secondRangeSize = lastRow1 + (lastRow2 - 3000)

    With Sheets("Sheet2")
        .Range("A1:M" & lastRow1) = Sheets(1).Range("A1:M" & lastRow1).Value
        .Range("A" & (lastRow1 + 1) & ":M" & secondRangeSize) = Sheets(1).Range("A3001:M" & lastRow2).Value
    End With

    With Sheets("Sheet2").Sort
        .SortFields.Add Key:=Range("B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange Range("A1:M" & secondRangeSize)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub