0

I can't seem to get my two open workbooks to set as variables. I have created a sub that opens the second one, so both now should be open.

But when I step through the code I get:

"rn-time error'438' object doesn't support this property or method.

I have tried both .Select and Activate. Both come back with errors. have included the sub that opens the second workbook, for reference.

Sub copytoMaster()

Dim wkbk As Workbook
Dim NewFile As Variant

NewFile = "C:\Users\msheppar\Desktop\new holiday project\Master Holiday Tracker.xlsm"

If NewFile <> False Then
Set wkbk = Workbooks.Open(NewFile)
End If

End Sub

Sub CopyAndPaste()

Dim wbpast As Workbook
Dim wbcop As Workbook
Dim xlastrowcopy As Long
Dim xlastrowpast As Long
Dim xlastcolumnn As Long
Dim i As Integer
Dim j As Integer
Dim r As Integer
Dim rname As String


Set wbcop = Workbooks("Holiday form (9).xlsm")

Set wbpast = Workbooks("Master Holiday Tracker.xlsm")

'For i = 1 To xlastrowcopy

wbcop = ActiveSheet
Sheets("sheet4").Select
Parfait
  • 104,375
  • 17
  • 94
  • 125
Max
  • 3
  • 2
  • 3
    `wbcop = ActiveSheet` doesn't make a lot of sense. What were you trying to do with that line? – BigBen Apr 30 '20 at 17:17
  • Open worksheet 4. But it is mainly that i dont seem to be able to set as objects as i will need to refer to them later in the code. I will be copying out of one & into the other. I appreciate that i could simply ask how to do this. But everyone on here is very good, & will do it all, & then i dont learn. I would rather do it badly and understand the process. Hopefully that makes sense – Max Apr 30 '20 at 17:29
  • I appreciate your eagerness to learn! That line doesn't make sense because the left-hand side `wbcop` is a `Workbook`, and the right-hand side `ActiveSheet` is not a workbook. – BigBen Apr 30 '20 at 17:31
  • [This question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) might be a good read for how to work with worksheet and workbook objects, referencing them properly and avoiding `Select`. – BigBen Apr 30 '20 at 17:31
  • 1
    Apart from the fact that your code is incomplete, you should make a couple of changes to it: 1) The `If NewFile <> False Then` condition is redundant, since you're setting `NewFile` to a string in the previous line of code, 2) `wbcop = ActiveSheet` should be `Set wbcop = ActiveSheet`. – Miqi180 Apr 30 '20 at 17:38
  • setting it as active worksheet was just desperation as i had tried everything elese. still get "run time error 13 type missmatch" when it gets to the line where i set the worksheet as a variable, code in next comment. – Max Apr 30 '20 at 18:44
  • Dim wbpast As Workbook Dim wspast As Worksheet Dim wbcop As Workbook Dim wscop As Worksheet Dim xlastrowcopy As Long Dim xlastrowpast As Long Dim xlastcolumnn As Long Dim i As Integer Dim j As Integer Dim r As Integer Dim rname As String Set wbcop = Workbooks("Holiday form (11) .xlsm") Set wscop = Workbooks(wbcop).Worksheets("worksheet4") xlastrowcopy = cells(Rows.Count, "A").End(xlUp).Row Workbooks(wbpast).Worksheets(wspast).cells(i, xlastrowcopy).Copy Workbooks(wbpast).Worksheets(wspast).cells(i, xlastrowcopy + 1).Paste – Max Apr 30 '20 at 18:47
  • Have you tried accesing them with an index like `Set wk = Workbooks(1)`? Also there is _never_ a need to use `.Select` and rarely to `.Activate`. – John Alexiou Apr 30 '20 at 19:49

0 Answers0