I almost have this macro working but I get the following error message:
Information cannot be pasted because copy area and the paste area are not the same size.
Here is the macro:
Sub GetTotalRow()
'
Sheets("Summary").Select
Cells.Find(What:="Totals", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Copy
ActiveCell.EntireRow.Select
Selection.Copy
Sheets("Holiday").Select
Range("x2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Update:I did not see all of your response. The word "Totals" will always appear in the row as well as values in the same row. Can you help me do a loop macro?
Answers & Comments
Verified answer
You are copying a cell, then overwriting what's on the clipboard and copying the entire row, then trying to paste the row into a single cell.
Either select the whole row that you want to paste in or just copy the cell and paste it into your target cell.
EDIT: Is the word "Totals" always in the same column? If so then you can write a loop to go through each row looking for a the value totals, then just copy and paste that row
EDIT2: I understand that the row will always be different depending on how many rows you are totaling, but I wanted to know if "Total" will always be in Column A for example.
Either way, you can just write a loop to check for the word "Total"
rowCount = 1;
totalRow = 0;
While Range("A" & rowCount).value <> "" AND totalRow = 0
if Range("A" & rowCount).value = "Total" Then totalRow = rowCount
rowCount = rowCount + 1
Wend
if totalRow = 0 Then
Msgbox "not found"
exit sub
End if
Rows(totalRow).select //not sure if this is valid, but just do something to select the row
Selection.Copy
Sheets("Holiday").Rows(2).Select
Selection.Paste
it particularly is slicing that off somewhat, yet once you have been to reference purely the values, you will pass formulation altogether, i.e.: Public Sub SetB3() Worksheets("Sheet1").variety("B3").fee = Worksheets("Sheet1").variety("B2").fee end Sub