Paste to multiple sheets

[Rubber Beaked Woodpecker]

Hi

I use the following code to copy prices on sheet1 and paste on sheet 7.

However I would also like to paste to sheets to sheets 8 and 9.

Is it possible to modify this code to do this?

Many thanks, RBW

Sub logPrice()
   
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyColumn As Long

Set source = Sheets("Sheet1")
Set destination = Sheets("Sheet7")

source.Range("Y5:Y16").Copy

emptyColumn = destination.Cells(44, destination.Columns.Count).End(xlToLeft).Column

If IsEmpty(destination.Range("A43")) Then
    destination.Cells(1, 1).PasteSpecial Transpose:=True
       
Else
    emptyColumn = emptyColumn + 1
    destination.Cells(44, emptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
End If

   
End Sub

 

[Micron]

maybe if you incorporate something like

Dim i as integer

Set source = Sheets("Sheet1")
source.Range("Y5:Y16").Copy

For i = 7 to 9
  Set destination = Sheets("Sheet" & i)

  rest of code here

Next

End Sub

 

[Micron]

In case you need clarification, I meant like this (untested of course)

Sub logPrice()
   
Dim destination As Worksheet
Dim emptyColumn As Long, i As Integer

Sheets("Sheet1").Range("Y5:Y16").Copy
For i = 7 To 9
  Set destination = Sheets("Sheet" & i)
  emptyColumn = destination.Cells(44, destination.Columns.Count).End(xlToLeft).Column
  If IsEmpty(destination.Range("A43")) Then
    destination.Cells(1, 1).PasteSpecial Transpose:=True
  Else
    emptyColumn = emptyColumn + 1
    destination.Cells(44, emptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
  End If
Next
   
End Sub

I omitted source variable because you only use it once and it would not be too onerous to just use what you set the variable value to. Put it back if you prefer.

 

[Rubber Beaked Woodpecker]

In case you need clarification, I meant like this (untested of course)

Sub logPrice()
 
Dim destination As Worksheet
Dim emptyColumn As Long, i As Integer

Sheets("Sheet1").Range("Y5:Y16").Copy
For i = 7 To 9
  Set destination = Sheets("Sheet" & i)
  emptyColumn = destination.Cells(44, destination.Columns.Count).End(xlToLeft).Column
  If IsEmpty(destination.Range("A43")) Then
    destination.Cells(1, 1).PasteSpecial Transpose:=True
  Else
    emptyColumn = emptyColumn + 1
    destination.Cells(44, emptyColumn).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
  End If
Next
 
End Sub

I omitted source variable because you only use it once and it would not be too onerous to just use what you set the variable value to. Put it back if you prefer.

Works great thank you

Not sure I understand the reason for following though;

'I omitted source variable because you only use it once and it would not be too onerous to just use what you set the variable value to. Put it back if you prefer.'

 

[Micron]

You created an object variable, then set its value to something (a particular sheet) then used that variable to get at a property of the object (e.g. a range value) so that you could get at the value from the range. I just got the value from the range, basically by using the same reference that you used to create the object. If you had used that object 2 or more times, or needed to get at 2 or more properties of the object then I might not have eliminated the variable. Doing so would have made it necessary to reference the object every time you needed it or any of its properties. If that's not clear, let me know.

If you consider this solved, can you mark it so? Thanks.

 

[Rubber Beaked Woodpecker]

You created an object variable, then set its value to something (a particular sheet) then used that variable to get at a property of the object (e.g. a range value) so that you could get at the value from the range. I just got the value from the range, basically by using the same reference that you used to create the object. If you had used that object 2 or more times, or needed to get at 2 or more properties of the object then I might not have eliminated the variable. Doing so would have made it necessary to reference the object every time you needed it or any of its properties. If that's not clear, let me know.

If you consider this solved, can you mark it so? Thanks.

Ok think that makes sense. I'll study more later. Once again thank you and this is now solved

 

[Rubber Beaked Woodpecker]