CODEWITHSUNDEEP
vbaexcel
Dopeyy2
Dopeyy2

Reputation: 27

Update sub while running

So This time my problem is trying update as it going. What is happening is when I get to Ht sheets. When I copy HT information over it goes over VT information that I had just placed. so is there anyway to fix this?

Private Sub CommandButton2_Click()

Dim queen As Range
Dim thought As Range
Dim one As Range
Dim waiting As Range
Dim prob As Range
Dim sign As Range

Set queen = Sheets("VT").Range("C2").End(xlDown)
Set thought = Sheets("VT").Range("C2").End(xlDown).Offset(0, -1)
Set one = Sheets("PalmFamily").Range("B1").End(xlDown)
Set waiting = one.End(xlToRight).Offset(1, 1)
Set prob = Sheets("HT").Range("C2").End(xlDown)
Set sign = Sheets("HT").Range("C2").End(xlDown).Offset(0, -1)

Sheets("VT").Activate
    Range("C2", queen).Select
    Application.CutCopyMode = False
    Selection.Copy
Sheets("PalmFamily").Activate
    one.Offset(1, 0).Select
    ActiveSheet.Paste
Sheets("VT").Activate
    Range("A2", thought).Select
    Application.CutCopyMode = False
    Selection.Copy
Sheets("PalmFamily").Activate
    waiting .Select
    ActiveSheet.Paste
Application.ScreenUpdating = True
Sheets("HT").Activate
    Range("C2", prob).Select
    Application.CutCopyMode = False
    Selection.Copy
Sheets("PalmFamily").Activate
    one.Offset(1, 0).Select
    ActiveSheet.Paste
Sheets("HT").Activate
    Range("A2", sign).Select
    Application.CutCopyMode = False
    Selection.Copy
Sheets("PalmFamily").Activate
    waiting.Select
    ActiveSheet.Paste

End Sub

Upvotes: 0

Views: 85

Answers (2)

Alex P
Alex P

Reputation: 12489

I think this gets you what you need. 

Private Sub CommandButton2_Click()

    Dim queen As Range, thought As Range, one As Range, waiting As Range, prob As Range, sign As Range

    Set queen = Sheets("VT").Range("C2").End(xlDown)
    Set thought = queen.Offset(0, -1)
    Set one = Sheets("PalmFamily").Range("B1").End(xlDown)
    Set waiting = one.End(xlToRight).Offset(1, 1)
    Set prob = Sheets("HT").Range("C2").End(xlDown)
    Set sign = prob.Offset(0, -1)

    Worksheets("VT").Range("C2", queen).Copy Destination:=one.Offset(1, 0)
    Worksheets("VT").Range("A2", thought).Copy Destination:=waiting
    Worksheets("HT").Range("C2", prob).Copy Destination:=one.End(xlDown).Offset(1, 0)
    Worksheets("HT").Range("A2", sign).Copy Destination:=waiting.End(xlDown).Offset(1, 0)

End Sub

Upvotes: 0

stenci
stenci

Reputation: 8481

You should always try not to use copy and paste in a macro.

Instead of writing this:

Sheets("VT").Activate
    Range("C2", q).Select
    Application.CutCopyMode = False
    Selection.Copy
Sheets("PalmFamily").Activate
    o.Offset(1, 0).Select
    ActiveSheet.Paste

You should write this:

Sheets("PalmFamily").Cells(R2, C2) = Sheets("VT").Cells(R1, C1)

This works after defining variables like:

'R1 and C1 are row and column of C2
Dim R1 As Long, C1 As Long
R1 = 2
C1 = 3

'R2 and C2 are row and column of B1
Dim R2 As Long, C2 As Long
R1 = 1
C1 = 2

When you work with variables pointing to rows and columns, it's easy to increment them when you need, with a simple R2 = R2 + 1.

Upvotes: 1

Related Questions