Copy Values from multiple sheets to first sheet on new line

[Hudco]

I have the following VBA code which works except part bolded. Basically I want the "control" sheet to summarise the data from the other sheets - the item specified in cell B7, the name of the sheet, the values from N7:Q7. The cells N7:Q7 are formula but I want the values calculated to be pasted into the control sheet. Appreciate any help.

Sub next1()
Sheets("Control").Select
ActiveSheet.Unprotect
Sheets("Control").Range("B7:C36").ClearContents
Dim oCell As Range
Dim ws As Worksheet
Set oCell = ThisWorkbook.Worksheets("Control").Range("C7")
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Control" And ws.Name <> "First" And ws.Name <> "End" And ws.Name <> "Loan" And ws.Name <> "NewSheet" Then
oCell.Value = ws.Name
oCell.Hyperlinks.Add Anchor:=oCell, Address:="", SubAddress:=ws.Name & "!A1"
Set oCell = oCell.Offset(1, 0)
End If
Next ws
Range("B7").Select
Dim bCell As Range
Dim wt As Worksheet
Set bCell = ThisWorkbook.Worksheets("Control").Range("B7")
For Each wt In ThisWorkbook.Worksheets
If wt.Name <> "Control" And wt.Name <> "First" And wt.Name <> "End" And wt.Name <> "Loan" And wt.Name <> "NewSheet" Then
bCell.Value = Range("B7").Value
bCell.Hyperlinks.Add Anchor:=bCell, Address:="", SubAddress:=wt.Name & "!B7", TextToDisplay:=wt.Range("B7").Value
Set bCell = bCell.Offset(1, 0)
End If
Next wt
Range("B7").Select
Dim LR As Long
LR = Range("B7").End(xlDown).Row
Range("B7").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Control").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Control").Sort.SortFields.Add Key:=Range("B7"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Control").Sort
.SetRange Range("B7:C36")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("D7:G36").Select
Selection.ClearContents
Range("D7").Select
Dim gCell As Range
Dim wa As Worksheet
Set gCell = ThisWorkbook.Worksheets("Control").Range("D7")
For Each wa In Worksheets
If wa.Name <> "Control" And wa.Name <> "First" And wa.Name <> "End" And wa.Name <> "Loan" And wa.Name <> "NewSheet" Then
wa.Range("N7:Q7").Copy
ActiveSheet.PasteSpecial xlPasteValues
Set gCell = gCell.Offset(1, 0)
End If
Next wa

Range("B7:C36").Select
Selection.Locked = False
Selection.FormulaHidden = False
Sheets("Control").Select
Range("D7").Select
ActiveSheet.Protect
ActiveWorkbook.Protect
End Sub

 

[MARK858]

Try changing the line to

Sheets("Control").Cells(Rows.Count, "D").End(xlUp)(2).PasteSpecial xlPasteValues

Change the column letter "D" to suit if needed (trying to decipher what you are doing in the code you should be able to remove the next line in the code).

 

[Hudco]

Hi Mark858, thanks, that worked well, however.... The amounts at N7 to Q7 are appearing on the "Control" sheet but are out of order to the name derived from B7 and the sheet names. So what happens is that on the control sheet I am wanting the following data from each sheet on one row - B7, Sheet name, Values of N7, O7, P7, Q7. The firs two items to be hyperlinked so the sheet is just a click away. The data on the control sheet is to be sorted based on the sheet name. Any suggestions

 

[MARK858]

I am unsure of your layout including what you are copying and what is already in the Control sheet so....

Please post some usable (not Jpegs etc.) screenshots (see my signature block for some links to addins to do this) of 2 of the sheets the data is coming from and a before and after of the Control sheet.

 

[Hudco]

Hi Mark, All good. I corrected the Sort code area and now works.

Than you very much.

Clyde

 

[Hudco]

Hi Mark I spoke to soon. I cannot seem to attach screen shots so I will try to describe.
The workbook has the control sheet as described and then new sheets are generated from a "template" sheet. These new sheets are for loans on equipment. The sheets all have the following:
B7 - Item of equipment
Sheetname - as it suggests
N7 to Q7 are payments and interest
These all need to be listed row by row starting at row 7 (cell B7)

I can get the details of B7 and the sheet names onto the "Control" sheet and hyperlinked so I can click on any sheet name or equipment name in the list and it takes me to the relevant sheet. What I am trying to also get is for each of the sheetnames in the list on the "control" sheet is the values from N7 to Q7 of each sheet - a summary.

Your code has enabled the copy of the values but instead of starting at cell D7 it starts at a cell 2 below the final list (row 36 is the last row of the list)

The following "Sheets("Control").Cells(Rows.Count, "D").End(xlUp)(2).PasteSpecial xlPasteValues" goes to the last cell in column D instead of D7.

Have I done something wrong?

Clyde

 

[MARK858]

The following "Sheets("Control").Cells(Rows.Count, "D").End(xlUp)(2).PasteSpecial xlPasteValues" goes to the last cell in column D instead of D7.

 Have I done something wrong?

Actually it goes to the first blank cell below the last cell with data in column D and it does that because your post title says "first sheet on new line" . If you want it fixed as D7 then put it as such...

Sheets("Control").PasteSpecial xlPasteValues

Which is basically what you already had

Without screenshots or a link to a file (with a before and after for the Control sheet) then I am afraid I can't help further.

 

[Rick Rothstein]

I have the following VBA code which works except part bolded.

wa.Range("N7:Q7").Copy
ActiveSheet.PasteSpecial xlPasteValues

I'll let Mark finish working through whatever code changes he is working with you on, but I just wanted let you know why the bolded line above did not work for you... you omitted the required range on the ActiveSheet for the paste to take place at. You cannot just paste the contents of the clipboard to the active sheet, you need to specify where on the active sheet it should be pasted... either the starting cell for the paste (usually the easiest to specify) or the exact correctly sized range for the paste.

 

[MARK858]

...but I just wanted let you know why the bolded line above did not work for you... you omitted the required range on the ActiveSheet for the paste to take place at

and unbelievably I just done the same mistake because I forgot to add the range in before posting...

Sheets("Control").PasteSpecial xlPasteValues

which should have read

Sheets("Control").Range("D7").PasteSpecial xlPasteValues

I can tell it's Sunday