Posts by danando
-
-
Re: Conditionally copy selected cells in rows depending on cell contents.
Hi Jindon,
Thanks for your help, I was able to get it to run on another workbook without any issues.
As we're currently pulling data based on a cell containing GT, how do I add more conditions so that I also pulls data if a cell in the same column contained TT or DT?
Thanks again,
Dan. -
Re: Conditionally copy selected cells in rows depending on cell contents.
This time I put the code in the ST_Production Schedule file, and ran the macro, and it worked!! Thank you so much!
I'm sorry for being such an idiot around excel and programming.Thanks again.
If I were to run this macro on other files, would it go into the next blank row within the GT&TT Trial.xlsm file?
-
-
-
-
-
Re: Conditionally copy selected cells in rows depending on cell contents.
I put the full path of the files within the code.
CodeSet ws1 = Workbooks("Z:\Scheduling Trial\ST_Production Schedule.xls").Sheets("SCHED") Set ws2 = Workbooks("Z:\Scheduling Trial\GT&TT Trial.xlsm").Sheets("Sheet1")
Now I get a "Run-time error '9' Subscript out of range." error.
-
Re: Conditionally copy selected cells in rows depending on cell contents.
Hi Jindon,
I tried the new code, and ran the new macro, now I get an error '400' with no pointer to which line of code it's breaking on.
However, if I try to run the macro while in VBA I get this error:
"Run-time error '1004'
Application-defined or object-defined error"
But that doesn't point to anything within the code either. -
-
Re: Conditionally copy selected cells in rows depending on cell contents.
Sorry, won't quote any more.
This is what I need to happen:
From a workbook named "ST_Production Schedule.xls" I would like it to read down from B4 down to data end, and if "GT" is found in a row, then from that row, copy data into "GT&TT Trial.xlsm"
Lets say "GT" is found in Row 20 (i.e. B20), then copy F20 from "ST_Production Schedule.xls" to the next completly available row in A Column in "GT&TT Trial.xlsm", then copy G20 from "ST_Production Schedule.xls" to B column in the same row as the previous row in "GT&TT Trial.xlsm", then copy G20 from "ST_Production Schedule.xls" to B column in the same row as the previous data in "GT&TT Trial.xlsm", then copy H20 from "ST_Production Schedule.xls" to C column in the same row as the previous data in "GT&TT Trial.xlsm", then copy J20 from "ST_Production Schedule.xls" to E column in the same row as the previous data in "GT&TT Trial.xlsm", then copy K20 from "ST_Production Schedule.xls" to F column in the same row as the previous data in "GT&TT Trial.xlsm", then copy M20 from "ST_Production Schedule.xls" to G column in the same row as the previous data in "GT&TT Trial.xlsm", then copy L20 from "ST_Production Schedule.xls" to I column in the same row as the previous data in "GT&TT Trial.xlsm", then copy M20 from "ST_Production Schedule.xls" to G column in the same row as the previous data in "GT&TT Trial.xlsm", then copy O20 from "ST_Production Schedule.xls" to H column in the same row as the previous data in "GT&TT Trial.xlsm".
Then, continue looking down B row until data end, and if any "GT" is found, repeat the paragraph above.
I hope this makes sence.
Dan.
-
Re: Conditionally copy selected cells in rows depending on cell contents.
I got rid of the period, and now I get a "Run-time error 9. Subscript out of range" error.
Then no line of the code is highlighted.
Current Code:
Code
Display MoreSub test() Dim rng As Range, r As Range, ws As Worksheet Set ws = Workbooks("ST_Production Schedule.xls").Sheets("SCHED") With ThisWorkbook.Sheets("Sheet1") Set rng = .Range("b1", .Range("b" & Rows.Count).End(xlUp)) End With For Each r In rng If r.Value Like "GT" Then With ws.Cells(Rows.Count, 1).End(xlUp).Offset(1) .Resize(, 3).Value = Array(r(1, 6).Value, r(1, 7).Value, r(1, 8).Value) .Offset(, 3).Resizer(, 2).Value = Array(r(1, 10).Value, r(1, 11).Value) .Offset(, 11).Value = r(1, 14).Value .Offset(, 7).Value = r(1, 15).Value End With End If Next Set ws = Nothing Set rng = Nothing End Sub
Quote from jindon;565019 -
Re: Conditionally copy selected cells in rows depending on cell contents.
Now it's getting a "Compile Error: Syntax Error" on
Current Code is:
Code
Display MoreSub test() Dim rng As Range, r As Range, ws As Worksheet Set ws = Workbooks.("ST_Production Schedule.xls").Sheets("SCHED") With ThisWorkbook.Sheets("Sheet1") Set rng = .Range("b1", .Range("b" & Rows.Count).End(xlUp)) End With For Each r In rng If r.Value Like "GT" Then With ws.Cells(Rows.Count, 1).End(xlUp).Offset(1) .Resize(, 3).Value = Array(r(1, 6).Value, r(1, 7).Value, r(1, 8).Value) .Offset(, 3).Resizer(, 2).Value = Array(r(1, 10).Value, r(1, 11).Value) .Offset(, 11).Value = r(1, 14).Value .Offset(, 7).Value = r(1, 15).Value End With End If Next Set ws = Nothing Set rng = Nothing End Sub
-
-
Re: Conditionally copy selected cells in rows depending on cell contents.
OK, corrected, but now when I run the macro, I get a "Object doesn't support this property or method" error, after ST_Production Schedule.xls is opened.
Current Code:
Code
Display MoreSub test() Dim rng As Range, r As Range, ws As Worksheet Set ws = Workbooks.Open("Z:\Scheduling Trial\ST_Production Schedule.xls").SCHED(1) With ThisWorkbook.Sheets("Sheet1") Set rng = .Range("b1", .Range("b" & Rows.Count).End(xlUp)) End With For Each r In rng If r.Value Like "*GT*" Then With ws.Cells(Rows.Count, 1).End(xlUp).Offset(1) .Resize(, 3).Value = Array(r(1, 6).Value, r(1, 7).Value, r(1, 8).Value) .Offset(, 3).Resizer(, 2).Value = Array(r(1, 10).Value, r(1, 11).Value) .Offset(, 11).Value = r(1, 14).Value .Offset(, 7).Value = r(1, 15).Value End With End If Next Set ws = Nothing Set rng = Nothing End Sub
Dan.
Quote from jindon;565011 -
Re: Conditionally copy selected cells in rows depending on cell contents.
Sorry Jindon, the code won't change again.
With the new code, I am getting a "Runtime error '438' Object doesnt support this property or method." on the line
Any Ideas?
Dan.
Quote from jindon;564996I'll give you the advice that when you ask the question in the forum, don't change the conditions until you test the code with the sample data.
If the code runs properly with it, ask to change the conditions. (I'm not even feel good though)
Otherwise, just wasting the time to me.
Don't think you can alter the code for yourself.
If you can, you are already able to write the code for yourself.(It is not like the formula system and even formula system differs)Code
Display MoreSub test() Dim rng As Range, r As Range, ws As Worksheet Set ws = Workbooks("Production Schedule.xls").SCHED(1) With ThisWorkbook.Sheets("sheet1") Set rng = .Range("b1", .Range("b" & Rows.Count).End(xlUp)) End With For Each r In rng If r.Value Like "*GT*" Then With ws.Cells(Rows.Count, 1).End(xlUp).Offset(1) .Resize(,3).Value = Array(r(1,6).Value,r(1,7).Value,r(1,8).value) .Offset(,3).Resizer(,2).Value = Array(r(1,10).Value, r(1,11).Value) .Offset(,11).Value = r(1,14).Value .Offset(,7).Value = r(1,15).VAlue End With End If Next Set ws = Nothing Set rng = Nothing End Sub
-
Re: Conditionally copy selected cells in rows depending on cell contents.
Hi Jindon,
Thanks for your help.
I'm now at the point where I run the Macro, ST_Production Schedule.xlsx opens, but then nothing gets copied over.
Im not sure if it's the code that brings the data over into the new workbook, as I'm not sure how to decipher this part of the macro:CodeFor Each r In rng If r.Value Like "*GT*" Then _ ws.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3).Value = r.Offset(, -3).Resize(, 3).Value
One of the scedules has changed now the data starts in Row 4, and the Column that would contain "GT" is in Column B.
So to confirm:
If a cell in Column B contains "GT", then data from that row needs to be copied over to a new blank row in the new workbook.
Column F to be copied to Column A
Column G to be copied to Column B
Column H to be copied to Column C
Column J to be copied to Column E
Column K to be copied to Column F
Column N to be copied to Column L
Column O to be copied to Column HHere is the code that I am currently working off:
Code
Display MoreSub test() Dim rng As Range, r As Range, ws As Worksheet Set ws = Workbooks.Open("Z:\Scheduling Trial\ST_Production Schedule.xls").Sheets(1) '<- With ThisWorkbook.Sheets("sheet1") Set rng = .Range("b4", .Range("b" & Rows.Count).End(xlUp)) End With For Each r In rng If r.Value Like "*GT*" Then _ ws.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3).Value = r.Offset(, -3).Resize(, 3).Value Next Set ws = Nothing Set rng = Nothing End Sub
Thanks for your help.
Dan. -
Re: Conditionally copy selected cells in rows depending on cell contents.
Quote from jindon;564493Doesn't explain your problem.
How is it not working ?
I get a "Run-time error '9': Subscript out of range" on the line:
Here is the code that I'm currently working with:
Code
Display MoreSub test() Dim rng As Range, r As Range, ws As Worksheet Set ws = Workbooks("Z:\Scheduling Trial\ST_Production Schedule.xls").SCHED(1) '<- With ThisWorkbook.Sheets("sheet1") Set rng = .Range("f1", .Range("f" & Rows.Count).End(xlUp)) End With For Each r In rng If r.Value Like "*GT*" Then _ ws.Cells(Rows.Count, 1).End(xlUp)(2).Resize(, 3).Value = r.Offset(, -3).Resize(, 3).Value Next Set ws = Nothing Set rng = Nothing End Sub
-
Re: Conditionally copy selected cells in rows depending on cell contents.
Quote from jindon;563560_
Code
Display MoreSub test() Dim rng As Range, r As Range, ws As Worksheet Set ws = Workbooks("TT Trial.xls").Sheets(1) '<- With ThisWorkbook.Sheets("sheet1") Set rng = .Range("f1", .Range("f" & Rows.Count).End(xlUp)) End With For Each r In rng If r.Value Like "*GT*" Then _ ws.Cells(Rows.Count,1).End(xlUp)(2).Resize(, 3).Value = r.Offset(, -3).Resize(, 3).Value Next Set ws = Nothing Set rng = Nothing End Sub
Hi Jindon,
I've given this a shot, but it doesn't seem to work, and I'm also unsure how the code actually works. Is there a chance you might be able to walk me though it?
Cheers,
Dan. -