Macro to paste from workbooks to another workbook

[dariushou]

So i have some code which opens up closed workbooks according to an excel list that i have created and then renames the individual worksheets according to the columns to the right of the list and then deletes all of the rows above a certain word in column A. Works like a charm and i thank those board memebers who helped me with this. I would now like to expand on the code to perform the task of copying certain cells from the sheets that i have renamed and pasting the values into another workbook. The new workbook has worksheet names that are identical to the file name of the sheets i'm copying from. I think it would be easier to walk you thru an example.

The excel list and code is here that works to rename and delete rows:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 75px"><COL style="WIDTH: 454px"><COL style="WIDTH: 68px"><COL style="WIDTH: 229px"><COL style="WIDTH: 68px"><COL style="WIDTH: 76px"><COL style="WIDTH: 73px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 67px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold"></TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet1</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet2</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet4</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet5</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet7</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet8</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Sheet10</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">File Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Full Path</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Name</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: left">01B</TD><TD>R:\Capital\01B</TD><TD>01B PY</TD><TD>01B Collat</TD><TD>01B XIO</TD><TD>01B P</TD><TD>01B M7B</TD><TD>01B M8</TD><TD>01B M9B</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: left">02A</TD><TD>R:\Capital\02A</TD><TD>02A PY</TD><TD>02A Collat</TD><TD>02A XIO</TD><TD>02A P</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 75px"><COL style="WIDTH: 454px"><COL style="WIDTH: 68px"><COL style="WIDTH: 229px"><COL style="WIDTH: 68px"><COL style="WIDTH: 76px"><COL style="WIDTH: 73px"><COL style="WIDTH: 72px"><COL style="WIDTH: 72px"><COL style="WIDTH: 60px"><COL style="WIDTH: 60px"><COL style="WIDTH: 67px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD><TD>L</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">33</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">File Name</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">Full Path</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: center">To copy</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">34</TD><TD style="FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: left">01B</TD><TD>R:\Capital\01B</TD><TD>C11</TD><TD>F4:F505 and N4:N505 and O4:O505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">35</TD><TD style="FONT-FAMILY: Arial Unicode MS; TEXT-ALIGN: left">02A</TD><TD>R:\Capital\02A</TD><TD>C11</TD><TD>F4:F505 and N4:N505 and O4:O505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD><TD>E4:E505</TD></TR></TBODY></TABLE>

Excel tables to the web >> Excel Jeanie HTML 4
<o></o>

Sub Renames_Files_Deletes_Rows()
Dim rng As Range, I As Long, ii As Long
With ActiveSheet
    Set rng = .Range("a1", .Cells.SpecialCells(11))
End With
For I = 4 To rng.Rows.Count
    If rng.Cells(I, 1).Value <> "" Then
        With Workbooks.Open(rng.Cells(I, 4).Value)
            For ii = 1 To .Sheets.Count
                If rng.Cells(I, ii + 4).Value = "" Then Exit For
                With .Sheets(ii)
                    .Name = rng.Cells(I, ii + 4).Value
                    myFind = IIf(ii = 1, "Price/Yield", "Cashflows")
                    On Error Resume Next
                    .Range("a1", .Columns("a").Find(myFind)).EntireRow.Delete
                    On Error GoTo 0
                End With
            Next
            .Close True
        End With
    Else
        Exit For
    End If
Next
End Sub

So this code above is telling excel to go thru the list in column D in the top table and rename each sheet in order according to the columns in E thru N. At the same time, it is deleting any rows in sheet one above the word "Price/Yield" in column A and for the rest of the sheets above the word "Cashflows". It then saves the file and closes it and moves to the next file. Works like a charm.

So i have set up a 2nd table in the list that has the cells in each sheet that i want to copy. (2nd table from above)

I'm not sure of the best way to set this up, but here is what i have in mind. After excel performs the above code, i would like it to go thru the 2nd list and reopen the corresponding file and copy cell C11 in the first sheet (this actually has to copy the result of the formula =-right(c11,8)) and then paste it in a workbook that has yet to be opened and is named "Master". The file "Master" is in the same location as the rest of the files. Then i would like it to move to the 2nd sheet, if there is one, and copy cells F4:F505, N4:O505 and paste them in the workbook "Master". For each file, the cells that i am copying from in each worksheet will have the values pasted into its on worksheet within the new workbook. For example, for the File 01B, cells C11 of the 1st worksheet, Cells F4:F505 and N4:O505 and for each sheet thereafter cells E4:E505 will be pasted into one sheet named 01B in the "Master" file. Each worksheet will have the same name as the filename of the file it is copying from.

Sorry for the long post, but i hope it is clear. Please let me know if you have any questions or need additional info.

Thanks!!
Darius

 

[dariushou]

Re: Macro to copy from workbooks and paste to another workbook

Any ideas? I'm stuck on this and can't find anything that works. I'm very new to vba.

 

[dariushou]

bump. Please help. I don't want to spend another weekend trying to figure just this out.

 

[dariushou]

I need to get this done by tomorrow. Any ideas? Any help would be much appreciated.