Copy and Paste

[jmk15315]

Good Evening,

I am currently using a spreadsheet that I did not write the vba code for a certain function, but hoping someone might be able to rtell me if the possibility exists to have it function slightly different.

I have a tab in the workbook that there is a current macro that will retrieve the information from a different spreadsheet (selected by the user) and copy data from a named range of cells, then paste it into the current spreadsheet.

I am wondering if there is a way to select multiple spreadsheets so the operation can happen all at once?

The information from the "Retrieved from" sheets will always reside in the same sub folder.

Public Sub LinkComposite()
  Dim wbSource As Workbook, wsSource As Worksheet, wsComposite As Worksheet
  Dim sSource As String, sComposite As String, iDataRow As Long, rFound As Range
 
  sComposite = ActiveWorkbook.Name
  If ActiveCell.Column <> 1 Then
    MsgBox "Please ensure the selected cell is after the header row and in column A", , "Incorrect Cell Selected"
    Exit Sub
  End If
  Set rFound = ThisWorkbook.Worksheets("Tooling").Range("B:B").Find("FREEFORM / SWAG", LookIn:=xlValues)
  If ActiveCell.Row > 2 And ActiveCell.Row < rFound.Row Then
    iDataRow = ActiveCell.Row
  Else
    MsgBox "Please ensure the selected cell is between the header row and the FREEFORM / SWAG Row" _
       & " and in column A", , "Incorrect Cell Selected"
    Exit Sub
  End If
  Application.Dialogs(xlDialogOpen).Show (ActiveWorkbook.Path)
 
  sSource = ActiveWorkbook.Name

  Set wbSource = Workbooks(sSource)
  Set wsSource = wbSource.Worksheets("Summary")
  Set wsComposite = ThisWorkbook.Worksheets("Tooling")
 
  With wsComposite
    wsSource.Range("Accounting_Info").Copy
    .Range("A" & iDataRow & ":M" & iDataRow).PasteSpecial xlPasteValues
   
  End With
 
  Windows(sComposite).Activate
  wbSource.Close SaveChanges:=False
 
  Range("A2").Select
 
  End Sub

The user selects the file to import data from, I am hoping that there is a way to allow for the selection of multiple files in order to save time. There are instances where the user needs to import 30 or more sets of data and this would cut down on the time to complete the task. Obviously I would want the information to come in from each spreadsheet and populate in there each rows. This might not even be possible, but hoping someone might be able to assist me with this.

 

[jmk15315]

SUCCESS!!!

That last addition corrected the issue. I can not tell you how much that will improve our process.

I do have one additional ask. Is there a way to include a line to enter a password? There is a push by corporate to possibly password encrypt the sheets that contain the data that I now have the ability to upload thanks to your assistance.

I have tested with a file that is encrypted, and it prompts me to enter the password before it continues, just did not know if it was possible to add a line in the code to resolve this for me automatically?
It may not be possible, but thought I would ask.

 

[Anthony47]

Workbooks.open provides the ability to provide a password; something like:

'before the loop:
Dim myPassw As String
myPassw = "CannotTellyou"           '<<< Your password                      ADDED LINE
'
'In the loop, modify Workbooks.Open:
    Workbooks.Open Filename:=fNames(I), ReadOnly:=True, Password:=myPassw   'MODIFY Workbooks.Open

However, this can only work if all the files to be opened have the same password, which seems unlikely to me

 

[jmk15315]

Actually the file with the data that is imported, is a "root" file so it has the same passcode encryption. I will test this in the morning when I get back to the office. Excited to make the department teams life much easier.

 

[jmk15315]

Thank you for your expertise. Your assistance has been appreciated beyond words.

 

[Anthony47]

Thank you for the feedback
If that resolves the problem then it'd be better to mark the discussion as Resoved; see the procedure: Mark as Solution