CODEWITHSUNDEEP
excelvba
user6932188
user6932188

Reputation:

why is my sub, deleting a row of data in my other sheet - VBA

I have 2 sheets "Ark1" and "Ark2". i get my data from "Ark2" and put them in "Ark1". my "Sub X" fuction should transpose the data from horizontal to vertical with the dates. But when i run "sub x" it is deleting a row of data in "Ark2" where i get my data. I have all the code in one module. 

    Sub x()

Dim lngDataColumns As Long
Dim lngDataRows As Long

lngDataColumns = 3
lngDataRows = 50


For t = 1 To lngDataRows

Range("l2").Offset(((t - 1) * lngDataColumns) - 1, 0).Resize(lngDataColumns, 1).Value = _
        Application.Transpose(Range("f1:h1").Value)

Range("M2").Offset(((t - 1) * lngDataColumns) - 1, 0).Resize(lngDataColumns, 1).Value = _
        Application.Transpose(Range("f1:h1").Offset(t).Value)

Next t

End Sub

Here is a pictuere of the deleted data.

deleted data

and here is a picture of what i wnat it to do in "Ark1" painted in colours.

i hope you can help

enter image description here

Upvotes: 0

Views: 35

Answers (2)

Error 1004
Error 1004

Reputation: 8230

You can use:

Option Explicit

Sub Test()

    With ws1
        .Range("A1").Select
    End With

End Sub

If you use the name outside the brackets:

enter image description here

or

Option Explicit

Sub Test()

    With ThisWorkbook.Worksheets("Sheet1")
        .Range("A1").Select
    End With

End Sub

you use the name in the brackets:

enter image description here

Upvotes: 0

Matteo NNZ
Matteo NNZ

Reputation: 12665

To avoid this kind of issues, always qualify your objects fully. Where you write Range("A1"), you should instead write: 

ThisWorkbook.Sheets("Your sheet").Range("A1")

Where:

  • ThisWorkbook qualifies the object in the workbook where the code is running (the default, instead, is ActiveWorkbook which might be something else in the moment where the code runs).
  • Sheets("Your sheet") makes sure you run in the good worksheet.

This is of course to be done everywhere you say just Range("something").

Tip: if you think your code would get heavy by doing this, then you can define your range on top of your code and use it by name lately, for example: 

Dim myRng1 As Range: Set myRng1 = ThisWorkbook.Sheets("Your sheet").Range("your range")
...
...
myRng1.DoSomething()
myRng1.DoSomethingElse()

Upvotes: 1

Related Questions