CODEWITHSUNDEEP
vbaexcel
Yigit Tanverdi
Yigit Tanverdi

Reputation: 161

Copying and pasting datas to the selected row

I have a code piece from my working code which copies and pastes datas from other Worksheets to one masterworkbooks mastersheet. The code below lets me copy and paste datas from column BX to column A's first empty row and does the same for column CC to column B's first empty row. However, I would like to paste the column CC to Column B's (10th) row. How can I do this?

 lRow = copySheet.Cells(copySheet.Rows.Count, 1).End(xlUp).Row

 With copySheet.Range("BX2:BX" & lRow)
   pasteSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
   .Resize(.Rows.Count, .Columns.Count) = .Value
 End With

 'Determine last row of Column B in copySheet
 lRow = copySheet.Cells(copySheet.Rows.Count, 1).End(xlUp).Row

 With copySheet.Range("CC2:CC" & lRow)
  pasteSheet.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
  .Resize(.Rows.Count, .Columns.Count) = .Value
 End With

Could you show me how I can determine how many rows are seletected to be copied?

Edit: Now I would like to add an if condition for another column, which should say:

if

column U in Worksheet "data" has cell value "8636" then these values should be pasted to Column H in Worksheet "KomKo"(pastesheet); to the next row as I used the code above in the "with" part.

Else( If the value in Column H is not 8636) then it should paste the

value inside this column to Column G at Worksheet "KomKo"(pastesheet) with same preferences as above again

.

How can I do this ?

Upvotes: 1

Views: 123

Answers (2)

Dawid SA Tokyo
Dawid SA Tokyo

Reputation: 376

Change pasteSheet.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value to that pasteSheet.Range("B10").Resize(.Rows.Count, .Columns.Count) = .Value

************* ANSWER TO QUESTION EDIT ****************
******* Added maxR - highest last row from column H and G *******
You could do something like this to get what you need: 

Sub check8636values()

    Dim copySheet, pasteSheet As Worksheet
    Dim lRowU, lRowH, lRowG, maxR, i As Long

    'Dont forget to change to the correct sheet names!!!!
    Set copySheet = ThisWorkbook.Sheets("data")
    Set pasteSheet = ThisWorkbook.Sheets("KomKo")

    lRowU = copySheet.Cells(copySheet.Rows.Count, "U").End(xlUp).Row

    For i = 1 To lRowU

        lRowG = pasteSheet.Cells(pasteSheet.Rows.Count, "G").End(xlUp).Row + 1
        lRowH = pasteSheet.Cells(pasteSheet.Rows.Count, "H").End(xlUp).Row + 1
        maxR = Application.Max(lRowG,lRowH)

        If copySheet.Cells(i, "U").Value = "8636" Then

            pasteSheet.Cells(maxR, "H").Value = copySheet.Cells(i, "U").Value
            pasteSheet.Cells(maxR, "Y").Value = copySheet.Cells(i, "T").Value

        Else

            pasteSheet.Cells(maxR, "G").Value = copySheet.Cells(i, "U").Value
            pasteSheet.Cells(maxR, "X").Value = copySheet.Cells(i, "T").Value

        End If

    Next i

End Sub

Upvotes: 2

user3598756
user3598756

Reputation: 29421

since you're dealing with one-column ranges only there's no need for the With-End With blocks to abbreviate the Resize method parameters: just use lRow for the first one only

moreover since you're not showing if copySheet and pasteSheet are from the same workbook it's safer to reference them before .Rows.Count, and prevent issue deriving from their source workbook excel version

    'Determine last row of Column B in copySheet
    lRow = copySheet.Cells(copySheet.Rows.Count, 1).End(xlUp).Row

    pasteSheet.Cells(pasteSheet.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(lRow) = copySheet.Range("BX2:BX" & lRow).Value        
    pasteSheet.Range("B10").Resize(lRow).Value = copySheet.Range("CC2:CC" & lRow).Value

Upvotes: 2

Related Questions