Help pasting cell values, not formulas...also how to sort merged cells, if possible

[TheBigOFace]

Long time lurker, first time poster,

I created a form on "Sheet 1" that utilizes a VLOOKUP function to fill specific cells.

This is my current code:
Sub CopyIngredients()
Sheets("Sheet 1").Select
Range("B18:R36").Select
Selection.Copy
Sheets("Sheet 2").Select
Range("B1").Select
Range("B1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End Sub

The cells in the "B18:R36" range have the cells that contain the VLOOKUP formula in them. I am trying to paste the cell results into "Sheet 2" using an offset to create a new list.

Currently, for some reason, in rows 3-5 it pastes the formula itself which gives me the "#N/A" error, but in rows 6+ I get the actual formula value pasted. Not sure why, I've tried deleting the affected rows, but keep getting the same error.

I've managed to figure out most of the above VBA coding by scouring these forums, but this has me stumped.

Secondly, is there a way to sort merged cells in a table where each column might have a different number of cells merged into one.


Thanks

 

[tlowry]

Welcome to MrExcel

Is this what you want?

    Sheets("Sheet 1").Range("B18:R36").Copy
    Sheets("Sheet 2").Range("B1").PasteSpecial xlPasteValues

Not sure what you want in:

Secondly, is there a way to sort merged cells in a table where each column might have a different number of cells merged into one.

Can you give an example?

 

[TheBigOFace]

Is this what you want?

    Sheets("Sheet 1").Range("B18:R36").Copy
    Sheets("Sheet 2").Range("B1").PasteSpecial xlPasteValues

That does work but where should I enter the code to tell it to find the next empty cell in Column B (I have text in cells C1:C2). I am basically creating a progressive list.

Range("B1").End(xlDown).Offset(1, 0).Select - I know that's how to select the cell I need, but I'm not sure how to incorporate that into the above code.

For the second part, I'd be sorting what was copied from the above code into something more manageable. I can record the macro to sort by filter, but it won't apply because B3:D3 is one merged cell, and E3:I3 is one merged cell. when I attempt to sort by layers, I get an error stating that the cells need to be the same width. If I sort an individual column, then I am unable to keep the data together. <----I hope that makes sense

 

[tlowry]

Option Explicit

'=========================================================
' Try this..
'=========================================================
'=========================================================
Sub oncemore()
    Dim dataRange, lr
    On Local Error Resume Next
    
    ' copy cells from sheet 1
    With Sheets("Sheet 1")
        .Range("B18:R" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy
    End With
    
    ' paste values and formats into sheet 2
    lr = Sheets("Sheet 2").Cells(Rows.Count, 2).End(xlUp).Row
    Sheets("Sheet 2").Range("B" & lr).PasteSpecial xlPasteValues
    Sheets("Sheet 2").Range("B" & lr).PasteSpecial xlPasteFormats
    
    ' sort on column B in sheet 2
    Sheets("Sheet 2").Sort.SortFields.Clear
    Sheets("Sheet 2").Sort.SortFields.Add _
            Key:=Range("B1:B" & lr), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet 2").Sort
        .SetRange Range("B1:D106")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    
    
    
End Sub