MS Excel VBA Copy and Paste from external workbook data to central worksheet

  • Thread starter Thread starter asxastro
  • Start date Start date
A

asxastro

I have a MASTER sheet which contains a table of records, which I want
a marco to essentially open a targeted external 'read only'
spreadsheet, copy specific cell data and paste the specific cell data
into a FINAL table located in another worksheet.

For each record information found in the table in the MASTER worksheet.
(see MASTER SHEET table)

1. Locate, open and validate if the 'READ ONLY' MS spreadsheet
'path_name' and 'worksheet' name.

FALSE Scenario

If either the 'path_name' or 'worksheet' does NOT exist, then use the
letter 'N' on the 'Extracted_Y_N' column, closed the targeted
spreadsheet and move onto the next record.

TRUE Scenario

If the 'path_name' and 'worksheet' DOES exist, then use the letter 'Y'
on the 'Extracted_Y_N' column.

Next, copy from the MASTER sheet the 'path_name' parameter from the
record and insert in column A in the FINAL sheet. This cell data will
be copied down by X number of times contingent on what number is on
Column 'F' from the MASTER table record.

Next, using the record details 'colm1', 'colm2', 'row_start' and
'records_to_copy' parameters in the record found in the MASTER file
table, the macro will copy the specific data from the open targeted
spreadsheet and place it into the 'Final' worksheet in columns B and
C.

Next, when all specific data has been copied to the FINALworksheet,
the Targeted spreadsheet will be closed.

2. Go to the next record in the MASTER table and Loop point 1 again.

Repeat this process until all records in the MASTER table records have
all been investigated or performed.

**********************************************************************************************************
'MASTER SHEET' table

path_name worksheet colm1 colm2 row_
Records_to_ Extracted

start copy_downwards _Y_N
-----------------------------------------------------------------------------------------------------------------------------------
C:\flder,A&B\J&C_A.xls sht&aa1 B D 8
4 Y
C:\flder,B&N\J&C_F.xls sht2 A C 5
7 Y
C:\abc\abc.xls sht2 A D
2 2 N
C:\flder,B&N\J&C_M.xls sht1 D G 8
5 Y

and many more records…


**********************************************************************************************************
FINAL SHEET (End result of what the 'FINAL' sheet would look like
this)

path_name Colm1_F Colm2_F
----------------------------------------------------------------------------------------
C:\flder,A&B\J&C_A.xls a a1
C:\flder,A&B\J&C_A.xls a1
C:\flder,A&B\J&C_A.xls
C:\flder,A&B\J&C_A.xls a a1
C:\flder,B&N\J&C_F.xls c
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_F.xls c c1
C:\flder,B&N\J&C_M.xls c c
C:\flder,B&N\J&C_M.xls c c
C:\flder,B&N\J&C_M.xls s s
C:\flder,B&N\J&C_M.xls
C:\flder,B&N\J&C_M.xls s s

**********************************************************************************************************

1st TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
-------------------------------------------------------------------
path_name : C:\flder,A&B\J&C_A.xls
Worksheet : sheet&aa1

Column B Column D
Row 8 a a1
Row 9 a1
Row 10
Row 11 a a1


2nd TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
------------------------------------------------------------------
path_name : C:\flder,B&N\J&C_F.xls
Worksheet : sheet2

Column A Column C
Row 5 c
Row 6 c c1
Row 7 c c1
Row 8
Row 9 c c1
Row 10 c c1
Row 11 c c1
Row 12 c c1

-------------------------------------------------------------------


3rd TARGETED External Spreadsheet the path and worksheet name was Not
Valid.

-------------------------------------------------------------------
4th TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
path_name : C:\flder,B&N\J&C_M.xls
Worksheet : sheet1

Column D Column G
Row 8 c c
Row 9 c c
Row 10 s s
Row 11
Row 12 s s

-------------------------------------------------------------------


Apreciate any help on this task, I'm a complete beginner in Excel VBA
programming and no sure if this task is doable.

MANY THANKS...
 
I have a MASTER sheet which contains a table of records, which I want
a marco to essentially open a targeted external 'read only'
spreadsheet, copy specific cell data and paste the specific cell data
into a FINAL table located in another worksheet.

For each record information found in the table in the MASTER worksheet.
(see MASTER SHEET table)

1. Locate, open and validate if the 'READ ONLY' MS spreadsheet
'path_name' and 'worksheet' name.

FALSE Scenario

If either the 'path_name' or 'worksheet' does NOT exist, then use the
letter 'N' on the 'Extracted_Y_N' column, closed the targeted
spreadsheet and move onto the next record.

TRUE Scenario

If the 'path_name' and 'worksheet' DOES exist, then use the letter 'Y'
on the 'Extracted_Y_N' column.

Next, copy from the MASTER sheet the 'path_name' parameter from the
record and insert in column A in the FINAL sheet.  This cell data will
be copied down by X number of times contingent on what number is on
Column 'F' from the MASTER table record.

Next, using the record details 'colm1', 'colm2', 'row_start' and
'records_to_copy' parameters in the record found in the MASTER file
table, the macro will copy the specific data from the open targeted
spreadsheet and place it into the 'Final' worksheet in columns B and
C.

Next, when all specific data has been copied to the FINALworksheet,
the Targeted spreadsheet will be closed.

2. Go to the next record in the MASTER table and Loop point 1 again.

Repeat this process until all records in the MASTER table records have
all been investigated or performed.

***************************************************************************­*******************************
'MASTER SHEET' table

path_name                     worksheet  colm1  colm2   row_
Records_to_         Extracted

start     copy_downwards  _Y_N
---------------------------------------------------------------------------­--------------------------------------------------------
C:\flder,A&B\J&C_A.xls   sht&aa1     B        D        8
4                         Y
C:\flder,B&N\J&C_F.xls   sht2           A        C         5
7                         Y
C:\abc\abc.xls                sht2           A        D
2          2                         N
C:\flder,B&N\J&C_M.xls  sht1           D        G        8
5                         Y

and many more records…

***************************************************************************­*******************************
FINAL SHEET (End result of what the 'FINAL' sheet would look like
this)

path_name                              Colm1_F             Colm2_F
---------------------------------------------------------------------------­-------------
C:\flder,A&B\J&C_A.xls              a                         a1
C:\flder,A&B\J&C_A.xls                                         a1
C:\flder,A&B\J&C_A.xls
C:\flder,A&B\J&C_A.xls              a                         a1
C:\flder,B&N\J&C_F.xls              c
C:\flder,B&N\J&C_F.xls              c                         c1
C:\flder,B&N\J&C_F.xls              c                         c1
C:\flder,B&N\J&C_F.xls
C:\flder,B&N\J&C_F.xls              c                         c1
C:\flder,B&N\J&C_F.xls              c                         c1
C:\flder,B&N\J&C_F.xls              c                         c1
C:\flder,B&N\J&C_F.xls              c                         c1
C:\flder,B&N\J&C_M.xls             c                         c
C:\flder,B&N\J&C_M.xls             c                         c
C:\flder,B&N\J&C_M.xls             s                         s
C:\flder,B&N\J&C_M.xls
C:\flder,B&N\J&C_M.xls             s                         s

***************************************************************************­*******************************

1st TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
-------------------------------------------------------------------
path_name : C:\flder,A&B\J&C_A.xls
Worksheet : sheet&aa1

                          Column B     Column D
       Row 8          a                 a1
       Row 9                              a1
       Row 10
       Row 11        a                  a1

2nd TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
------------------------------------------------------------------
path_name : C:\flder,B&N\J&C_F.xls
Worksheet : sheet2

                         Column A      Column C
      Row 5          c
      Row 6          c                   c1
      Row 7          c                   c1
      Row 8
      Row 9          c                   c1
      Row 10        c                   c1
      Row 11        c                   c1
      Row 12        c                   c1

-------------------------------------------------------------------

3rd TARGETED External Spreadsheet the path and worksheet name was Not
Valid.

-------------------------------------------------------------------
4th TARGETED External Spreadsheet, worksheet name and targeted cells
to be copied.
path_name : C:\flder,B&N\J&C_M.xls
Worksheet : sheet1

                         Column D       Column G
       Row 8         c                    c
       Row 9         c                    c
       Row 10       s                    s
       Row 11
       Row 12       s                    s

-------------------------------------------------------------------

Apreciate any help on this task, I'm a complete beginner in Excel VBA
programming and no sure if this task is doable.

MANY THANKS...

Excel can do al"If desired, send your fileS to dguillett @gmail.com I
will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
most anything
 
Back
Top