CODEWITHSUNDEEP
excelvba
alprieto
alprieto

Reputation: 59

VBA Excel: Copying cells without overwriting

I'm new with vba excel.

I have the sheet Sheet1 with this:

Sheet1

And I want that in other sheet (Sheet2) the data of the first sheet is copied but asking the especific number of Sheet1, by asking I mean column A number, which can be captured as a cell range or can be written in the code. What I have is very raw it goes like this and in effect is copying:

Sub planilla()
    Sheets("Sheet1").Range("A1:B14").Copy
    Sheets("Sheet2").Select
    Range("A2").PasteSpecial xlPasteValues
End Sub

The previous code is used when a button I have is clicked, so for example, if the case is 1 then I would expect this to be copied in Sheet2:

Sheet2

When the case is number 2 or 5, I don't want it to start in the cell A:2, I need it to start from cell A:10, for example if the case was 1 and then is 5 the result should look like this:

Sheet2 Expected

Not in all cases the range of start would be the same, for example if I try with numbers 2 and 5, my starting cell for 5 would be A:4, the only cell constant for sure will be A:2.

Upvotes: 0

Views: 1825

Answers (1)

Wizhi
Wizhi

Reputation: 6549

This might be a start for you.

You can build range as:

Range(A1:D1) -> Range(Cells(A1), Cells(D1)) -> 
Range(Cells(row number, column number), Cells(row number, column number)) -> 
Range(Cells(1, 1), Cells(1, 4))

Press F8 to go line by line in your code to see what happens at each line.

VBA Code:

Sub CopyValues()
Dim ws1 As Worksheet
Set ws1 = ActiveWorkbook.Worksheets("Sheet1") 'Set the name of worksheet "Sheet1"
Dim ws2 As Worksheet
Set ws2 = ActiveWorkbook.Worksheets("Sheet2") 'Set the name of worksheet "Sheet2"
Dim lrow As Long
Dim lrow2 As Long
Dim Number As Variant
Dim i As Long

lrow = ws1.Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row in Sheet1
lrow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row in Sheet2

Number = Application.InputBox(Prompt:="Enter a number", Type:=1) 'Input box where you write which number to copy from column A
'Number = 2


For i = 1 To lrow 'Loop through column A, from row 1 to lastrow
    If ws1.Cells(i, 1).Value = Number Then 'If cell value in column A is equal to your number then...
        ws2.Cells(lrow2 + 1, 1).Value = ws1.Cells(i, 1).Value 'Then copy the current row from Column A to the other sheet, first empty row in column A, 
        ws2.Cells(lrow2 + 1, 2).Value = ws1.Cells(i, 2).Value 'Then copy the current row from Column B to the other sheet, in column B, 
        lrow2 = lrow2 + 1 'Add one incremental to the last row.
    End If
Next i 'Go to next row number

End Sub

Upvotes: 1

Related Questions