Macro - copy row from 1 worksheet to another

[Goliath.ro]

Hello

I am kinda stuck, I am a noob at excel coding

I want 1 row from sheet 2 copied to first empty row from sheet 1 but aligned to the right as in:

sheet 2:

  A  B  C  D  E  F  G  H  
1    1  2  3  4  5  6  7  
2   ab cd ef 
3   ab ad cd df

would be copied in sheet 1

  A  B  C  D  E  F  G  H  
1    1  2  3  4  5  6  7  
2               ab cd ef 
3            ab ad cd ef

So far I was thinking to do a macro like this ( keep in mind this macro is incomplete and for clarification my example sheet 2 contains 2 rows when in reality its the same row that will be copied over and over with different data in the first empty row in sheet 1)

Sheets("Sheet2").Select
Range("B2").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sheet1").Select
Range("H2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select

and now I want it to offset to the left depending on ho many cells are writen in sheet 2

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Any1 have any sugestions ?

 

[jbeaucaire]

Something like this:

Option Explicit

Sub CopyShiftedToRight()
Dim wsOut As Worksheet
Dim Rw As Long, LR As Long, Cnt As Long, LC As Long

Set wsOut = Sheets("Sheet2")
With Sheets("Sheet1")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
    
    For Rw = 2 To LR
        wsOut.Rows(Rw).ClearContents
        Cnt = .Rows(Rw).SpecialCells(xlConstants).Cells.Count
        .Range("A" & Rw).Resize(, Cnt).Copy wsOut.Cells(Rw, LC).Offset(, -Cnt + 1)
    Next Rw
End With

End Sub

Or this:

        wsOut.Cells(Rw, LC).Offset(, -Cnt + 1).Resize(, Cnt).Value = _
                .Range("A" & Rw).Resize(, Cnt).Value