Copying an Offset-Cell from a different File

Question

I want to copy an offset cell which was first counted down in a list.

I am counting down, until the end +1, on the right side and then an offset from there to the DOWN,LEFT cell. And that number in the cell I want to copy into another file

Here is the code that does not work. I know I use quite a lot of select and selection, which is probably the reason why it is not working.

Workbooks("Purchase Order Details for FY 2015.xlsx").Activate
         Worksheets("2015").Select
Range("B333").Select
        Selection.Copy
         Windows("Technology Purchase Order Template V4.xlsm").Activate
         Sheets("PO Authorization").Select
         Range("B5").Select
         ActiveSheet.Paste

I did not say, that I got an error......It just doesn't do what I want it to do. — Rekboy, Jul 30 '15 at 08:27

score 0 · Answer 1 · answered Jul 29 '15 at 13:17

0

Replace Window with Workbooks:

 Workbooks("Technology Purchase Order Template V4.xlsm").Activate

and maybe Sheets with Worksheets

answered Jul 29 '15 at 13:17

Kᴀτᴢ

2,146
6
29
57

score 0 · Answer 2 · edited May 23 '17 at 11:58

0

Use

     Workbooks.open("Technology Purchase Order Template V4.xlsm").Activate

instead of

     Windows("Technology Purchase Order Template V4.xlsm").Activate

If you want to hide the opening/closing process, check out this answer: Open Excel file for reading with VBA without display

edited May 23 '17 at 11:58

Community

1
1

answered Jul 29 '15 at 13:29

Tom K.

1,020
1
12
28

score 0 · Answer 3 · answered Jul 30 '15 at 06:58

Sry that wasn't the Programm...Here is what I meant:

         Workbooks.Open("Purchase Order Details for FY 2015.xlsx").Activate
         Worksheets("2015").Select

         Selection = Worksheets("2015").Cells(Worksheets("2015").Rows.Count, "C").End(xlUp).Row
         Selection.Offset(1, -1).Copy

         Workbooks.Open("Technology Purchase Order Template V4.xlsm").Activate
         Worksheets("PO Authorization").Select
         Range("B5").Select
         ActiveSheet.Paste
         Sheets("SBB").Select
         Range("A2:W2").Select
         Selection.Copy

         Workbooks.Open("Purchase Order Details for FY 2015.xlsx").Activate
         Worksheets("2015").Select
         Selection = Cells(Worksheets("2015").Rows.Count, "C").End(xlUp).Row + 1
         Selection.PasteSpecial Paste:=xlPasteValues

Can somebody fix this please....you can see that I want to copy an offset cell into a file and from that file a range of cells into the bottom of a list with this Count(xlUp or xlDown) Function.

I hope somebody can solve this!

Erik

score 0 · Answer 4 · answered Jul 30 '15 at 11:11

Here is the Correct Code!

There are also some more functions in there so don't wonder.

Sub All_to_PDF_File()
    Dim Filename As String
    Dim sFile As String, sPath As String
    Dim wBook As Workbook
    Dim x As Long
    Dim y As Long

      On Error Resume Next
      Set wBook = Workbooks("Purchase Order Details for FY 2015.xlsx")

      If wBook Is Nothing Then 'Not open

      MsgBox "Please Open 'Purchase Order Details for FY 2015.xlsx' before running this macro!!"

      Set wBook = Nothing

      On Error GoTo 0

      Else 'It is open

         Workbooks("Purchase Order Details for FY 2015.xlsx").Activate
         Worksheets("2015").Select

         With ActiveSheet
            y = .Cells(.Rows.Count, "C").End(xlUp).Row
         End With

         Range("B" & y + 1).Select
         Selection.Copy

         Workbooks("Technology Purchase Order Template V4.xlsm").Activate
         Worksheets("PO Authorization").Select
         Range("B5").Select
         ActiveSheet.Paste

         Workbooks("Technology Purchase Order Template V4.xlsm").Activate
         Worksheets("SBB").Select
         Range("A2:W2").Select
         Selection.Copy

         Workbooks("Purchase Order Details for FY 2015.xlsx").Activate
         Worksheets("2015").Select

         With ActiveSheet
            x = .Cells(.Rows.Count, "C").End(xlUp).Row
         End With

         Range("C" & x + 1).Select
         Selection.PasteSpecial Paste:=xlPasteValues


         Workbooks("Technology Purchase Order Template V4.xlsm").Activate
         Worksheets("PO Authorization").Select



    'Call the function with the correct arguments
    Filename = RDB_Create_PDF(ActiveWorkbook, "", True, True)

    'For a fixed file name and overwrite it each time you run the macro use
    'RDB_Create_PDF(ActiveWorkbook, "C:\Users\Ron\Test\YourPdfFile.pdf", True, True)

    If Filename <> "" Then
        'Ok, you find the PDF where you saved it
        'You can call the mail macro here if you want
    Else
        MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
               "Microsoft Add-in is not installed" & vbNewLine & _
               "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
               "The path to Save the file in arg 2 is not correct" & vbNewLine & _
               "You didn't want to overwrite the existing PDF if it exist"
    End If
    End If


End Sub

Copying an Offset-Cell from a different File

4 Answers4