hi guys
the issue I am having is that I am combining a couple of reports and want most of my data to come from the sheet titled Wipedrive reports,
but what I am trying to do (with little success) is if any monitors are recorded on the asset capture sheet it copies the ATS# and Serial# across to the GRN report. This is becoming difficult because of the sums and lookups I have already put in place, any help or tips would be greatly appreciated.
I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"
-
-
-
Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"
You want to copy information from sheet "Asset Capture", from row 35, columns A & B to sheet "GRN Status Report" last row and columns L & J
Is it?
No duplicate to remove ? -
Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"
hello PCI and thanks for responding.
yes, I only want the data from sheet "asset capture", columns A and B starting at row 35 if column C contains the word MONITOR to populate "GRN status report" last row and columns L and J. the problem I have is that "GRN status report" column L is being populated manually when certain work is done on other assets which then grabs data from "wipedrive report". I'm trying to get the monitor data across because we don't get any monitor data from the wipedrive report. -
Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"
ok I have moved forward and all I am trying to do now is get column A, row 35 and down data on "asset capture" to move to "GRN status reports" column L, row 9 and down if the data on "asset capture" column 3, row 35 and down =MONITOR while leaving "GRN status reports" column L blank so raw data can be imputed when required.
-
Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"
can anyone have a quick look over this and steer me in the right direction?
I am trying to copy the asset numbers from "Asset Capture" column A to "GRN Status Report" column L, if Column C in "Asset Capture" contains the text "MONITOR"Code
Display MoreSub Test() For Each Cell In Sheets("Asset Capture").Range("C35:C3000") If Cell.Value = "MONITOR" Then matchRow = Cell.Row Rows.Range(matchRow & ":" & matchRow).Select Selection.Copy Sheets("GRN Status Report").Select lastRow = ActiveSheet.UsedRange.Rows.Count If lastRow > 1 Then lastRow = lastRow + 1 ActiveSheet.Range("L" & lastRow).Select ActiveSheet.Paste Sheets("Asset Capture").Select End If Next End Sub
-
-
Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"
Another way
Code
Display MoreSub Treat() Dim LR As Long Dim InSh As Worksheet, OutSh As Worksheet Dim WkRg As Range Const KeyW As String = "MONITOR" Const FR As Integer = 34 Const WkCol1 As String = "A" Const WkCol2 As String = "I" Const WkColOut As String = "L" Set InSh = Sheets("Asset Capture") Set OutSh = Sheets("GRN Status Report") LR = OutSh.UsedRange.Rows.Count LR = Range("A2").End(3).Row With InSh If (.AutoFilterMode) Then .AutoFilterMode = False ' REMOVE AUTOFILTER IF EXIST LR = .Cells(Rows.Count, WkCol1).End(3).Row Set WkRg = .Range(.Cells(FR, WkCol1), .Cells(LR, WkCol2)) WkRg.AutoFilter Field:=3, Criteria1:=KeyW LR = OutSh.Cells(Rows.Count, WkColOut).End(3).Row WkRg.Offset(1, 0).Copy Destination:=OutSh.Cells(LR + 1, WkColOut) WkRg.AutoFilter ' REMOVE AUTOFILTER End With End Sub
-
Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"
thank you PCI
this is working way better then my try but it is pasting extra data that i don't need, is there anyway i can modify this so that after filtering the data it only pastes the date from column A to column L?
i have made a few modifications i hope you don't mind.
Sub Treat()
Dim LR As Long
Dim InSh As Worksheet, OutSh As Worksheet
Dim WkRg As Range
Const KeyW As String = "MONITOR"
Const FR As Integer = 34
Const WkCol1 As String = "A"
Const WkCol2 As String = "C"
Const WkColOut As String = "L"
Set InSh = Sheets("Asset Capture")
Set OutSh = Sheets("GRN Status Report")
LR = OutSh.UsedRange.Rows.Count
LR = Range("A2").End(3).Row
With InSh
If (.AutoFilterMode) Then .AutoFilterMode = False ' REMOVE AUTOFILTER IF EXIST
LR = .Cells(Rows.Count, WkCol1).End(3).Row
Set WkRg = .Range(.Cells(FR, WkCol1), .Cells(LR, WkCol2))
WkRg.AutoFilter Field:=3, Criteria1:=KeyW
LR = OutSh.Cells(Rows.Count, WkColOut).End(3).Row
WkRg.Offset(1, 0).Copy Destination:=OutSh.Cells(LR + 8, WkColOut)
WkRg.AutoFilter ' REMOVE AUTOFILTER
End With
End Sub -
Re: I am trying to copy data from sheet 1 to sheet 2 if colum c contains "MONITOR"
jest3r,
Please take care when you post code to add code tags: I put it them for the first code you posted but for the last one there is some color tags which confused: Please update
" i hope you don't mind." => The code sent is yours now you can do what ever you want.
"it only pastes the date from column A to column L?" I hope my code was doing it is not the case ??
Please send an update of your file and show what is the final result you need
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!