Hi!
I'm trying to copy the value of one workbook and pasting it in another workbook. Where it is pasted depends on the value in the first workbook.
Both parts function correctly if run individually. However, when I run them together, my code in the first part is applied to Workbook2. Can anyone explain to me why this happens?
Any help would be much appreciated.
I'm trying to copy the value of one workbook and pasting it in another workbook. Where it is pasted depends on the value in the first workbook.
Code:
Sub Part1()
Dim rng As Range
Dim rng1 As Range
Dim Lastrow As Integer
Dim lCol As Long, i As Long
Lastrow = ActiveSheet.UsedRange.Rows.Count
Windows("Workbook1").Activate
Sheets("Sheet1").Select
lCol = Cells(9, Columns.Count).End(xlToLeft).Column
For i = lCol To 3 Step -1
If Cells(9, i) > 0 Then Cells(9, i).Activate
ActiveCell.Offset(1).Resize(Lastrow, 2).Select
Selection.Copy
'Part 2
Lastrow = ActiveSheet.UsedRange.Rows.Count
pnum = Workbooks("Workbook1").Sheets("Sheet1").Cells(9, i).Value
Windows("Workbook2").Activate
Sheets("Sheet1").Select
Set rng = Cells.Find(What:=pnum, After:=Range("D6"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
Set rng1 = rng.Offset(2, -1)
Cells.Find(What:="FY", After:=rng1, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1).Resize(Lastrow, 2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Next i
End Sub
Both parts function correctly if run individually. However, when I run them together, my code in the first part is applied to Workbook2. Can anyone explain to me why this happens?
Any help would be much appreciated.
Last edited by a moderator: