English to VBA Translator needed!

[cme]

Hi MrExcel community!

I am trying to convert the following into a VBA code Macro for Excel:

For all worksheets in this workbook besides sheet1 (titled "Summary"),
From row 14 onward, if there are values, copy all rows from Column B to Column G and paste into "Summary" sheet.
For each row copied to "Summary" sheet, copy value in cell C4 from the source worksheet into column A of "Summary" sheet.
For each row copied to "Summary" sheet, copy value in cell E4 from the source worksheet into column H of "Summary" sheet.

 

[cme]

Can anyone help with this? Really need help!

 

[cme]

Hi again,

Perhaps this is a better explanation:

I have been trying to solve this problem for weeks! I am trying to create a macro (to assign to a button) that will, for all worksheets in the workbook except "summary" sheet, looks at the rows on or after row 14 and if there is a value in the "C" column in the row then to copy that row from column 'B' to column 'G' and paste it into the "summary" sheet starting at row 5 and offset a few columns to the right, e.g. column 'D' to column 'I'.


Additionally there are some "static-location" cells with information associated to those rows that I want to copy into each row copied into summary sheet.


For example:
Sheet 1 = Summary
Sheet 2 = ProjectOne


On the sheet "ProjectOne" in cell 'A1' is the project name, in cell 'B1' is the project date, and in cell 'B3' is the project manager; and there are values in cells "B14:G70" (i.e. rows 14 to 70 and from column B to G).


When I run the macro, the summary sheet would copy "B14:G70" from "ProjectOne" and paste them into "Summary" at "D5:I61" and then copy cell 'A1', 'B1', and 'B3' from "ProjectOne" and paste them next to each of those copied rows respectively in columns 'A', 'B', and 'C'.


Then it would repeat for every worksheet in the workbook.


Thank you in advance for your help.

 

[WhoCanDo]

Does this suit:

Sub Summarise()
'
    Sheets("Summary").Select
    PageCount = Worksheets.Count
    j = 5 ' first paste location
    For i = 2 To PageCount
        Range(Cells(j, 1), Cells(j + 56, 1)).Value = Sheets(i).Cells(1, 1) ' project name
        Range(Cells(j, 2), Cells(j + 56, 2)).Value = Sheets(i).Cells(1, 2) ' date
        Range(Cells(j, 3), Cells(j + 56, 3)).Value = Sheets(i).Cells(3, 2) ' project manager
        Sheets(i).Range("B14:G70").Copy Cells(j, 4) ' data from lines 14 to 70
        j = j + 57 ' step to next paste location
    Next i
End Sub

 

[cme]

Hi WhoCando!

Unfortunately that didn't do the trick, my fault though I think I need to explain this better:

I need to copy information from every worksheet in the workbook (aside from the summary sheet), the number of worksheets will change.

I need to copy only the rows that have information in them, though every worksheet is the same format, some may have 10 rows of info, some may have 70, some may have none.

Then, for every row it copies to the summary, in order to know where it came from I need to know the related project title, project manager, etc. and that information is stored at the top of each worksheet and is unique to just that worksheet.

So lets say 'ProjectOne' has this info:

Program1	Bob Taylor	Dec 15, 2012
Date	Action	Responsibility	Comments	Code
1/1/2013	Buy	Bob		ABC
1/2/2013	Sell	Jane	sell half only	DEF

<tbody>
</tbody>

Then, in the 'Summary' sheet the macro will produce:

Summary
Proj. Name	Proj. MR	Created	Date	Action	Resp.	Comments	Code
Program1	Bob Taylor	Dec 15, 2013	1/1/2013	Buy	Bob		ABC
Program1	Bob Taylor	Dec15, 2013	1/2/2013	Sell	Jane	sell half only	DEF

<tbody>
</tbody>

Then it would repeat this for all the remaining sheets in the workbook by appending them to the subsequent rows.

Does this make sense?

 

[WhoCanDo]

Does this work:

Sub Summarise_1()
'
    Sheets("Summary").Select
    PageCount = Worksheets.Count
    J = 5 ' first paste location
    Range(Cells(J, 1), Cells(2000, 9)).ClearContents ' clear the summary page from line J down
    For i = 2 To PageCount ' for each page
        Sheets(i).Range("B14:G70").Copy Cells(J, 4) ' data from lines 14 to 70
        Lastrow = ActiveSheet.Range("D65536").End(xlUp).Row ' find last used line in column D
        Range(Cells(J, 1), Cells(Lastrow, 1)).Value = Sheets(i).Cells(1, 1) ' project name
        Range(Cells(J, 2), Cells(Lastrow, 2)).Value = Sheets(i).Cells(3, 2) ' project manager
        Range(Cells(J, 3), Cells(Lastrow, 3)).Value = Sheets(i).Cells(1, 2) ' date
        J = Lastrow + 1 ' set next paste location
    Next i
End Sub

Regards