Macro prompts User to choose source workbook from folder and paste to destination workbook template

[Turk182]

Hello everyone.
I need help with a macro. In this Macro, the user is prompted to CHOOSE a workbook file ( a version of ”Vendor Dec Sheet”) from his/her desktop folder (it is not necessary to actually open the file, but information in a certain range of this SOURCE workbook (“Fiber” tab, range F9:AR193) is copied and pasted to a DESTINATION workbook that is an always-open template.

The DESTINATION workbook's range for the initial paste will be in column C (also “Fiber” tab), offset one row down from the last row already occupied.

The selected SOURCE workbook also has a “Cover Page” tab where the vendor’s name (in Cell C6) is copied then pasted in the DESTINATION workbook, Cell B6, beside (to the left of) every row of data that was just pasted in the previous step (and only in those rows).

So far, I have this (the "primary" macro): ...

Sub Get_Data_from_File()

Dim FileToOpen As Variant
Dim openbook As Workbook
Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename(Title:="Browse for your file & Click/Select it", ButtonText:="Choose Supplier Dec Sheet that You Saved")
',FileFilter:="Excel Files ( .*XLS*), *XLS*")
If FileToOpen <> False Then
Set openbook = Application.Workbooks.Open(FileToOpen)
openbook.Sheets("Fiber Components").Range("F9:AR193").Copy
ThisWorkbook.Worksheets("Fiber data").Range("c8").PasteSpecial xlPasteValues
openbook.Sheets("Plastic Components").Range("B9:X193").Copy
ThisWorkbook.Worksheets("Plastics data").Range("c8").PasteSpecial xlPasteValues
openbook.Sheets("Foam Components (EPE, EPU, EPS)").Range("B9:W193").Copy
ThisWorkbook.Worksheets("Plastic Foam data").Range("c8").PasteSpecial xlPasteValues
openbook.Close False

End If
Application.ScreenUpdating = True

End Sub

...plus another macro to copy and fill down the supplier's name-- eventually wanting to combine the two into one button.

Thx,
Turk182

 

[airforceone]

care you give us dummy table using XL2bb to see if we can help you

 

[airforceone]

use the code below to open source/target file (.xls*)

    Dim cTr As Integer, i As Integer
    Dim FilterType As String, Cap_One As String, TargetFileName As String, SourceFileName As String

    FilterType = "Text files (*.xlsx),*.xlsx"
    Cap_One = "Select [ SOURCE ] FILE "
    SourceFileName = Application.GetOpenFilename(FilterType, , Cap_One)
    If SourceFileName = "False" Then
        MsgBox "Kindly Locate the SOURCE File location " & _
        vbNewLine & "NO FILE was SELECTED Exiting.....", vbCritical + vbOKOnly, ".."
        Exit Sub
    End If

 

[airforceone]

sample code for your Open File Dialog

Sub Turk182()
   
Application.ScreenUpdating = False

   
    Dim cTr As Integer, i As Integer
    Dim FilterType As String, Cap_One As String, TargetFileName As String, SourceFileName As String
    Dim wbTARGET As Workbook, wbSOURCE As Workbook
    Dim ws_TARGET As Worksheet, ws_SOURCE As Worksheet

    FilterType = "Text files (*.xlsx),*.xlsx"
   
    Cap_One = "Select [ SOURCE ] FILE "
    SourceFileName = Application.GetOpenFilename(FilterType, , Cap_One)
    If SourceFileName = "False" Then
        MsgBox "Kindly Locate the SOURCE File location " & _
        vbNewLine & "NO FILE was SELECTED Exiting.....", vbCritical + vbOKOnly, ".."
        Exit Sub
    End If

    Cap_One = "Select [ TARGET ] FILE "
    TargetFileName = Application.GetOpenFilename(FilterType, , Cap_One)
    If TargetFileName = "False" Then
        MsgBox "Kindly Locate the TARGET File location " & _
        vbNewLine & "NO FILE was SELECTED Exiting.....", vbCritical + vbOKOnly, ".."
        Exit Sub
    End If

    Set wbSOURCE = Workbooks.Open(SourceFileName)
    wbSOURCE.Worksheets("sheet name here").Activate
    Set ws_SOURCE = wbSOURCE.Worksheets("sheet SOURCE name here")
   
    Set wbTARGET = Workbooks.Open(TargetFileName)
    wbTARGET.Worksheets("sheet name here").Activate
    Set ws_TARGET = wbTARGET.Worksheets("sheet TARGET name here")

    'SOURCE Copy - TARGET Paste Code Here

Application.ScreenUpdating = True

End Sub

 

[Turk182]

Dim cTr As Integer, i As Integer Dim FilterType As String, Cap_One As String, TargetFileName As String, SourceFileName As String FilterType = "Text files (*.xlsx),*.xlsx" Cap_One = "Select [ SOURCE ] FILE " SourceFileName = Application.GetOpenFilename(FilterType, , Cap_One) If SourceFileName = "False" Then MsgBox "Kindly Locate the SOURCE File location " & _ vbNewLine & "NO FILE was SELECTED Exiting.....", vbCritical + vbOKOnly, ".." Exit Sub End If

Hi airforceone.

The following line of code produced runtime error 1004: method get openfilename of object_application failed
SourceFileName = Application.GetOpenFilename(FilterType, , Cap_One)

I apologize; I meant to mention that I am working on a MacBook (my online search revealed that MacBooks have problems with that particular method)

 

[airforceone]

I apologize; I meant to mention that I am working on a MacBook (my online search revealed that MacBooks have problems with that particular method)

If that's the case anyone Mac Office user might help you out

 

[Fluff]

I meant to mention that I am working on a MacBook

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Turk182]

If that's the case anyone Mac Office user might help you out

thanks

 

[Turk182]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

thanks; updated