0

I can't find the error in this code (from this answer) The macro is triggered when a button in the already opened "Pub" file is clicked All works until the copy from another file. Where is my error? Thank you in advance

Sub Foo()

Dim S As Worksheet, MM As Worksheet
Dim Pub As Workbook, Net As Workbook
Dim folderPath As String 
Dim Boat As String, SPub As String, SNet As String, SNetinPub As String
Dim LastRow As Long

Set Pub = ActiveWorkbook
Set S = Sheets("Main Sheet")

Boat = S.Cells(4, 9).Value
Set MM = Sheets(Boat) 'retrieve and select a sheet in the current "Pub" file

With MM 'Find the last filled row
    LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
End With

S.Cells(1, 7) = LastRow
S.Cells(2, 7) = LastRow - 5 + 27
SPub = "A6:F" & LastRow
SNet = "F6:F" & LastRow
SNetinPub = "G28:G" & S.Cells(2, 7).Value
MsgBox SNetinPub 'Check: it is really correct

Sheets(Boat).Range(SPub).Copy
With ActiveSheet.Range("A28")
    .PasteSpecial xlPasteFormats
    .PasteSpecial xlPasteValues
End With 'This copy all inside the "Pub file" works

'open the "Net" file in the same path
folderPath = Application.ActiveWorkbook.Path
Set Net = Workbooks.Add(folderPath & "\Net.xls")

'Method 1
Net.Sheets(Boat).Range(SNet).Copy
Pub.S.Range(SNetinPub).PasteSpecial 'ERROR HERE

'Method 2
'Net.Sheets(Boat).Range(SNet).Copy
'With Pub.S.Range("G28")
'   .PasteSpecial xlPasteFormats 'OR ERROR HERE
'   .PasteSpecial xlPasteValues
'End With

Net.Close True 'I don't wont to close Pub file after the macro

End Sub
marcoresk
  • 1,837
  • 2
  • 17
  • 29
  • 1
    It's just `S.Range(SNetinPub).PasteSpecial` and not `Pub.S.Range(SNetinPub).PasteSpecial` – Rory Oct 19 '17 at 07:51
  • Also, the size (# of rows and columns) of sNet must be identical to sNetInPub – jkpieterse Oct 19 '17 at 07:55
  • @jsotola Actually, it's just that `Pub` (activeworkbook) doesn't have a property named `S`. – Rory Oct 19 '17 at 07:59
  • @Rory. thank you, now it works. But I do not understand: The Pub file has a sheet called "Main Sheet", if S.Range works into Pub file, why Pub.S.Range is wrong? – marcoresk Oct 19 '17 at 08:17
  • 1
    For the workbook `Pub`, it's still the worksheet `Main sheet`, it doesn't get a new property `S` by declaring a variable with this name (how could it?). `S` is a variable of type worksheet and points to the sheet `Main sheet`, so you can work with that. If you for any reason want to work with the workbook, you have to write `Pub.Worksheets("Main sheet").Range(...)` – FunThomas Oct 19 '17 at 08:32

0 Answers0