Application.CutCopyMode=False on inactive sheet

[Millsio88]

Hi All,

Newbie to VBA and I'm struggling to resolve an issue. I'm running a module with a button on sheet 1 it copies data to sheet 2 & 3 when it gets to the last copies (Bold) the range remains highlighted on the sheets 2 & 3. I've tried using Application.CutCopyMode = False which only seem to work when the sheet is active. My only way around so far that I've found online is using a 'With' (Below) but i don't want the macro to select sheet 2, then sheet 3, to then select sheet1 at the end.

The result I would like is, once the Marco is complete, on both sheet 2 and 3 the next available cell in column A is selected - not the last data that was copied to them.

With Sheets("Data Log")
.Select
.Range("A1").select
End With

Any assistance would be great.

***Code***

Sub CopySource()
Dim rngSource As Range
Dim rngTarget As Range
Dim iRow As Integer

'Data
'start new row
iRow = Worksheets("Data Log").Cells(Rows.Count, 1).End(xlUp).Row + 1

Set rngSource = Worksheets("Submit Form2").Range("H9:H18")
Set rngTarget = Worksheets("Data Log").Range("A" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True

Set rngSource = Worksheets("Submit Form2").Range("E25:M25")
Set rngTarget = Worksheets("Data Log").Range("L" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues

Set rngSource = Worksheets("Submit Form2").Range("E30:K30")
Set rngTarget = Worksheets("Data Log").Range("U" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues

Set rngSource = Worksheets("Submit Form2").Range("E35:I35")
Set rngTarget = Worksheets("Data Log").Range("AB" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues

Set rngSource = Worksheets("Submit Form2").Range("E40:M40")
Set rngTarget = Worksheets("Data Log").Range("AG" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues

Set rngSource = Worksheets("Submit Form2").Range("Q8:Q20")
Set rngTarget = Worksheets("Data Log").Range("AP" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True

'start new row
iRow = Worksheets("Reg Log").Cells(Rows.Count, 1).End(xlUp).Row + 1

'Actions with transpose
Set rngSource = Worksheets("Submit Form2").Range("H9:H18")
Set rngTarget = Worksheets("Reg Log").Range("A" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks:=False, Transpose:=True

Set rngSource = Worksheets("Submit Form2").Range("E26:M26")
Set rngTarget = Worksheets("Reg Log").Range("L" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues

Set rngSource = Worksheets("Submit Form2").Range("E31:K31")
Set rngTarget = Worksheets("Reg Log").Range("U" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues

Set rngSource = Worksheets("Submit Form2").Range("E36:I36")
Set rngTarget = Worksheets("Reg Log").Range("AB" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues

Set rngSource = Worksheets("Submit Form2").Range("E41:M41")
Set rngTarget = Worksheets("Reg Log").Range("AG" & iRow)
rngSource.Copy
rngTarget.PasteSpecial Paste:=xlPasteValues

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
Try it like

Sub CopySource()
   Dim wsData As Worksheet
   Dim iRow As Long

   Set wsData = Sheets("Submit Form2")

   With Worksheets("Data Log")
      iRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
      .Range("A" & iRow).Resize(, 2).Value = Application.Transpose(wsData.Range("H9:H18").Value)
      .Range("L" & iRow).Resize(, 9).Value = wsData.Range("E25:M25").Value
      .Range("U" & iRow).Resize(, 7).Value = wsData.Range("E30:K30").Value
      .Range("AB" & iRow).Resize(, 5).Value = wsData.Range("E35:I35").Value
      .Range("AG" & iRow).Resize(, 9).Value = wsData.Range("E40:M40").Value
      .Range("AP" & iRow).Resize(, 13).Value = Application.Transpose(wsData.Range("Q8:Q20").Value)
   End With
End Sub

 

[Millsio88]

Thank you this has worked perfectly. Less grey hairs now!

 

[Fluff]

You're welcome & thanks for the feedback.