macro to copy data from sheet 'Data" cell F5 to last column containing data in row1 on sheet "summary"

[howard]

I need code that will copy data from sheet ''Data" cell F5 to last column containing data in row1 on sheet "summary" for eg if the last column containing datain row 1 is H1, then the data must be copied from F5 in sheet data and pasted into I1 in sheet summary


I have code but need it to be amended to suit my requirements

the code below copies the data to the last row in Col A.


It would be appreciated if someone could kindly copy the data

 Sub Copy_Data()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Data")
Set pasteSheet = Worksheets("Summary")

copySheet.Range("F5").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[wideboydixon]

Public Sub Copy_Data()

Application.ScreenUpdating = False

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim lastRow As Long
Dim lastCol As Long

Set copySheet = Worksheets("Data")
Set pasteSheet = Worksheets("Summary")

lastRow = pasteSheet.Cells(pasteSheet.Rows.Count, "A").End(xlUp).Row
lastCol = pasteSheet.Cells(lastRow, pasteSheet.Columns.Count).End(xlToLeft).Column

pasteSheet.Cells(lastRow, lastCol + 1).Value = copySheet.Range("F5").Value

Application.ScreenUpdating = True

End Sub

WBD

 

[howard]

Thanks for the help. The value in F5 in being copied to sheet summary in the last row in Col C. it must be copied after the last value in row1 for eg if H1 has a value, it must be copied into I1


Kindly check & amend your code

 

[wideboydixon]

Sorry; I misunderstood your requirement. If it's always row 1 then this:

Public Sub Copy_Data()

Application.ScreenUpdating = False

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim lastCol As Long

Set copySheet = Worksheets("Data")
Set pasteSheet = Worksheets("Summary")

lastCol = pasteSheet.Cells(1, pasteSheet.Columns.Count).End(xlToLeft).Column

pasteSheet.Cells(1, lastCol + 1).Value = copySheet.Range("F5").Value

Application.ScreenUpdating = True

End Sub

WBD

 

[howard]

Thanks for the help, code works perfectly

 

[howard]

thanks once again for all the help. I would like code to be amended so that if the last column in Row 1 = F5 on sheet "Data" , then exit sub, otherwise continue with the macro as per your code supplied i.e. to copy the data after the last column in row 1 containing data



your assistance regarding this is most appreciated

 

[wideboydixon]

So don't copy if it's already there?

Public Sub Copy_Data()

Application.ScreenUpdating = False

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet
Dim lastCol As Long

Set copySheet = Worksheets("Data")
Set pasteSheet = Worksheets("Summary")

lastCol = pasteSheet.Cells(1, pasteSheet.Columns.Count).End(xlToLeft).Column

If pasteSheet.Cells(1, lastCol).Value <> copySheet.Range("F5").Value Then
    pasteSheet.Cells(1, lastCol + 1).Value = copySheet.Range("F5").Value
End If

Application.ScreenUpdating = True

End Sub

WBD

 

[howard]

Your interpretation is 100% correct

Thanks for the help, code works perfectly