CODEWITHSUNDEEP
vbaexcel
Richard
Richard

Reputation: 1

Reading ranges into arrays and copying the values onto two separate worksheets

I would like to copy the values of two ranges which are read into two different variant variables onto two different worksheets. The code only copies the values if one of the destination worksheets is active and does not copy the values of both ranges at all. I would like the code to run successfully without any of these two worksheets being active.

Furthermore, if I do not include a On Error Resume Next bit in the code then it throws a Run-time error 1004 occassionally. I do not see why it is fickle.

Would you please let me know about your thoughts on how to get it to run as desired?

    Sub historical()

    With Application

    .ScreenUpdating = False: .DisplayAlerts = False: .DisplayStatusBar = False: .EnableEvents = False: .Calculation = xlCalculationAutomatic: .AskToUpdateLinks = False

   End With

   Dim wb As Workbook: Set wb = Workbooks("Tardiness")

   Dim wk1 As Worksheet: Set wk1 = wb.Worksheets("loginlogout") 'Source worksheet 1
   Dim wk2 As Worksheet: Set wk2 = wb.Worksheets("auxcodes") 'Source worksheet 2
   Dim wk4 As Worksheet: Set wk4 = wb.Worksheets("loginlogouth") 'Destination worksheet 1
   Dim wk5 As Worksheet: Set wk5 = wb.Worksheets("auxcodesh") 'Destination worksheet 1

   Dim lastrow4 As Long
   Dim lastrow5 As Long

   lastrow1 = wk1.Range("A" & Rows.Count).End(xlUp).Row
   lastrow2 = wk2.Range("A" & Rows.Count).End(xlUp).Row

   Debug.Print lastrow4, lastrow2

   On Error Resume Next

   lastrow5 = wk5.Range("A" & Rows.Count).End(xlUp).Row

   Dim aux() As Variant

   aux = wk2.Range("A5:AR" & lastrow2).Value

  wk5.Range(Cells(lastrow5 + 1, 1), Cells(lastrow5 + UBound(aux), UBound(aux, 2))).Value = aux

  Erase aux

  Dim loginout() As Variant

  loginout = wk1.Range("A2:K" & lastrow1).Value

  lastrow4 = wk4.Range("A" & Rows.Count).End(xlUp).Row

  wk4.Range(Cells(lastrow4 + 1, 1), Cells(lastrow4 + UBound(loginout), UBound(loginout, 2))).Value = loginout

  lastrow4 = wk4.Range("A" & Rows.Count).End(xlUp).Row

  Debug.Print lastrow4

  With wk4.Range("C2:F" & lastrow4)

 .NumberFormat = "hh:mm:ss"

 End With

 With wk4.Range("I2:K" & lastrow4)

 .NumberFormat = "hh:mm:ss"

 End With

 Erase loginout

With Application

.ScreenUpdating = False: .DisplayAlerts = False: .DisplayStatusBar = False: .EnableEvents = False: .Calculation = xlCalculationAutomatic: .AskToUpdateLinks = False

End With

End Sub

Upvotes: 0

Views: 49

Answers (1)

Vityata
Vityata

Reputation: 43585

In stead of:

wk5.Range(Cells(lastrow5 + 1, 1), Cells(lastrow5 + UBound(aux), UBound(aux, 2))).Value = aux

Try with:

With wks5
    .Range(.Cells(lastrow5 + 1, 1), .Cells(lastrow5 + UBound(aux), UBound(aux, 2))).Value = aux
end with

Cells is a variable, representing a Worksheets object, thus the object should be referred as well. Otherwise, it takes the activesheet object and an error can happen - e.g. the Range and the Cell can be considered to have different parents.

Info from here: https://msdn.microsoft.com/en-us/library/office/ff194567.aspx

Upvotes: 1

Related Questions