CODEWITHSUNDEEP
excelvba
eurano
eurano

Reputation: 55

Paste range of cells to first empty row and empty columnn

What I'm trying to achieve is to copy 3 first cells from sheet "Arkusz1" and paste it sheet "Arkusz2" to first empty column and first empty row in that column. After this the process repeats until first row will be filled with 10 cells then copying proceed in next row. I have code which pastes it to the first empty row in column A but it doesn't work properly as described above. I will appreciate any help.

Sub y()
LastRowy = Sheets("Arkusz2").UsedRange.Rows.Count
lastCol = Sheets("Arkusz2").Cells(LastRowy, Columns.Count).End(xlToLeft).Column

Set targetRNg = Worksheets("Arkusz1").Range("A4")

 
With Excel.ThisWorkbook.Sheets("Arkusz2")
  colCount = Arkusz2.UsedRange.Rows(LastRowy).Columns.Count
End With
MsgBox colCount
If colCount > 10 Then GoTo Line1 Else GoTo Line2
    
Line1:

With Excel.ThisWorkbook.Sheets("Arkusz2")
    Set destRng = .Cells(LastRowy, .Columns.Count).End(Excel.xlToLeft).Offset(0, 1).Resize(targetRNg.Rows.Count, targetRNg.Columns.Count)
    destRng.Value = targetRNg.Value

End With

Upvotes: 0

Views: 417

Answers (1)

Yogesh
Yogesh

Reputation: 134

I'd avoid using statements like - Goto Line 2 Instead, read excel and find out how many cells do you need to copy and create a loop to repeat that many times.

Following statement looks incorrect to me:

colCount = Arkusz2.UsedRange.Rows(LastRowy).Columns.Count

You can use following functions to get to the row that you want and read/set values accordingly

Function fn_GetLastColumn(ByVal sSheetName As String, ByVal iRow As Integer) As Integer
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets(sSheetName)
    fn_GetLastColumn = sht.Cells(iRow, sht.Columns.Count).End(xlToLeft).Column

End Function

Function fn_GetLastRow(ByVal SheetName As String, ByVal iColNo As Integer) As Integer
    Dim sht As Worksheet

    Set sht = ThisWorkbook.Sheets(SheetName)

    'Ctrl + Shift + End
    fn_GetLastRow = sht.Cells(sht.Rows.Count, iColNo).End(xlUp).Row

End Function

Upvotes: 0

Related Questions