Macro to take data from a file other than the current

[RIC63]

I NEED TO AUTOMATE AN OPERATION TO PUT DATA INTO A WORKBOOK FROM ANOTHER, I HAVE TRIED WITH THE MACRO RECORDER AND ALSO LOOKING VARIOUS SAMPLES ON THE WEB BUT JOINING TOGHETER PIECES OF CODE IS NOT SIMPLE FOR ME DUE MY POOR EXPERIENCE IN WRITING

VBA

CODE . IF ANYONE CAN HELP ME IN THIS HARD -FOR ME- EXERCISE I WILL APPRECIATE :



I have two files, the first one named Source.xlsx -from which i want take data- and a second one named Destination.xlsx where the selected data must be copied.


Source.xlsx is made of hundred of rows, I will put an * (asterisk) beside each row of my interest and that sign -or others- will be used to select the desired row from which copy specific column to put in specific columns / rows of Destination.xlsx….



Thank you for your asample.jpgnswer

[SamT]

I'M SORRY, I CAN"T HEAR YOU!

[SamT]

It is considered very rude to use ALL CAPITAL LETTERS in a message. All Caps mean that you are angry and are yelling.

Ok. All is forgiven and we continue.



This code will be simple, except that...

To clear up a language difficulty... it is not possible to place an * or any other sign "beside a row." all such signs must be in a Cell in a Row.

Which Column of Cells will you place the sign in?

I know that sometimes there is no available column then you must Format a Cell in the Row in a different color. You must tell us if this will be and which column will have the different color.

VBA code can "see" colors and clear them after "reading' them.

[RIC63]

First of all Thanks for your support
I intend to flag the row of my interest in the column L - as can be viewed in the attached image, lines for which I put a sign are the 11, 14 and 15.
Anyway the L column can be used for my purpose and I can use for this purpose any character, color, etc.

Thanks again

[SamT]

You will have to put the name of the destination sheet in this code.

Option Explicit

Sub LoadProjectDocumentList()
'Loads selected Client Documents into the Project Document list
'Use any keyboard character in column L to select the desired
'documents. Any value displayed by a formula will select a document.

Const Sproj As Long = 2  'Document number column on Source
Const Stype As Long = 3  'same
Const Snum As Long = 4   'same
Const Sdoc As Long = 7   'Doc Description on Source
Const Dcli As Long = 1   'Client Document Column on Destination
Const Ddoc As Long = 3   'Document Column on Destination

Dim Check As Range       'a Cell in column L on Source
Dim Srw As Long          'Active Row on Source
Dim Drw As Long          'Active Row on Destiantion

Dim Src As Worksheet
Dim Dest As Worksheet
Dim LastRow As Long

''''Initialize Variables
'*************************************************************
Set Src = Workbooks("Source.xlsx").Sheets("Document List")
Set Dest = Workbooks("Destination.xlsx").Sheets("*****************NAME OF DESTINATION WORKSDHEET HERE**********************") 'Edit to fit
'***************************************************************

Set Check = Src.Range("L8")
Drw = Dest.Cells(Rows.Count, Dcli).End(xlUp).Row + 1
LastRow = Src.Cells(Rows.Count, "L").End(xlUp).Row

''''Perform the work
  Do While Check.Row <= LastRow
    If Check.Value <> "" Then
      Srw = Check.Row
      With Src
       Dest.Cells(Drw, Dcli) = .Cells(Srw, Sproj) & .Cells(Srw, Stype) & .Cells(Srw, Snum)
       Dest.Cells(Drw, Ddoc) = .Cells(Srw, Sdoc)
      End With
      Drw = Drw + 1
    End If
    Set Check = Check.Offset(1)
  Loop

End Sub

[RIC63]

Hello SamT


the code you gave me works perfectly and I thank you again


would prefer only that - regardless of rows flagged in Source, the macro started to compile the Destination from row 13 (the first free) while currently starts from row 11


Thanks for your patience
riccardo

[SamT]

That is my mistake. I did not see hidden (almost) Row 12 in the screenshot.

Edit this line

Drw = Dest.Cells(Rows.Count, Dcli).End(xlUp).Row + 1

To this

Drw = 13

[RIC63]

Great Sam, now is perfect


again sincere thanks