I have a macro that opens another workbook(Stats) and pastes 4 columns of data into columns N, O, P, Q on Sheet1 which works great.
I am trying to write a worksheet change event in sheet1 that will copy only the rows that contain data in those 4 columns to the next blank row in sheet5
This code works fine if I copy and paste the data into columns N,O,P,Q on sheet1 but if I let the macro open the workbook and paste the data then it shows an error in this line
If wshS.Range("N" & s).Value <> "" Then
Here is the code that I have so far. Any ideas?
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Set wshS = Worksheets("Sheet1")
m = wshS.Range("N" & wshS.Rows.Count).End(xlUp).Row
Set wshT = Worksheets("Sheet5")
t = wshT.Range("A" & wshT.Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For s = 1 To m
If wshS.Range("N" & s).Value <> "" Then
wshS.Range("N" & s).Resize(ColumnSize:=13).Copy
t = t + 1
wshT.Range("A" & t).PasteSpecial Paste:=xlPasteValues
End If
Next s
Application.CutCopyMode = False
Application.ScreenUpdating = True
I am trying to write a worksheet change event in sheet1 that will copy only the rows that contain data in those 4 columns to the next blank row in sheet5
This code works fine if I copy and paste the data into columns N,O,P,Q on sheet1 but if I let the macro open the workbook and paste the data then it shows an error in this line
If wshS.Range("N" & s).Value <> "" Then
Here is the code that I have so far. Any ideas?
Dim wshS As Worksheet
Dim wshT As Worksheet
Dim s As Long
Dim m As Long
Dim t As Long
Set wshS = Worksheets("Sheet1")
m = wshS.Range("N" & wshS.Rows.Count).End(xlUp).Row
Set wshT = Worksheets("Sheet5")
t = wshT.Range("A" & wshT.Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
For s = 1 To m
If wshS.Range("N" & s).Value <> "" Then
wshS.Range("N" & s).Resize(ColumnSize:=13).Copy
t = t + 1
wshT.Range("A" & t).PasteSpecial Paste:=xlPasteValues
End If
Next s
Application.CutCopyMode = False
Application.ScreenUpdating = True