Posts by jamiespotts

Re: Copy data from certain cells on sheet, carry to another sheet, clear after copyin

Quote from AAE;635422

Hmmm . . . I wonder: will you continue to use this free service and access the workbook examples, code, etc. that is freely available - even though *YOU* have chosen to keep you solution to yourself? After all, there have been quite a number of users who have found their own solution and were big enough to share it with the community. I guess it depends on your level of maturity.

I absolutely will not use this free service again; although, I have in the past with great experiences. I would take the time out to say some additional remarks back to your response, but quite frankly I've wasted enough time on this thread and you really are not worth my time to verbally rip apart via the internet (or ever for that matter). My level of maturity is clearly being rated through 1 bad experience and me expressing my feelings towards the situation. Nevertheless, your responses speak volumes about your quick to judge attitude and lack of empathy. Thanks for your input and reassurance that this was just "lost in the mix". Again, have a wonderful week.

Re: Copy data from certain cells on sheet, carry to another sheet, clear after copyin

I removed it because there is no reason to share my work if no one is willing to say anything in regards to it, just view it and ignore my request for assistance...

1. I understand there are ways to shrink a file to make it the proper size...
2. I know how to ZIP compress a file, fact is it was still too large (by 134kb to be exact) to upload.
3. I used our private domain hosting to "share" the file, but nobody wanted to provide their input.

No worries! I figured it out on my own and resolved the VBA issue myself. Am I going to share this with other people after the way I have been ignored and treated? Absolutely not. Ban me for this, do whatever you will, but I will not be taken advantage of. Have a great week!

Re: Copy data from certain cells on sheet, carry to another sheet, clear after copyin

When you want to get things done you must do them yourself, eh?

I am looking for a macro that will take information from the sheet named "Worksheet" and place it in the corresponding column and row of sheet "Roll Information". From sheet named "Worksheet", I need the following data carried over to the "Roll Information" sheet:

1. LOT Number
2. Roll Number
3. Footage
4. Description
5. Width
6. Date
7. Description Tag #1
8. Tag #
9. Footage
10. Description Tag #2
11. Tag #2
12. Footage

Once this information is carried over to the "Roll Information" sheet, I need it to clear out sheet named "Worksheet" in the cells it took the information from. I could record the macro, but it becomes extremely laggy when I go to run it and I am unsure why. Any help is appreciated, thank you!

http://www.griffassoc.com/GDF/UV Coater Tracking2 JS.xlsm
^ Link to the workbook because it was too large to upload here. Again, I think this is because of my recorded macros... Please help!

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

I am trying to make e3,e7,e11,e15,e19,e23,e27 show down column f on the tracking workbook, but it is not doing that it is only showing 1 of the 7 cells down column f on the tracking workbook.

[vb]Split("e3,e7,e11,e15,e19,e24,e27", ",")[/vb]

instead of all those cells being pulled into the tracking workbook, only cell e27 is showing down column f.

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

[vb]Sub test()
Dim myDir As String, fn As String, ws As Worksheet, myRng
Dim i As Long, e, n As Long, Mine As Worksheet, x
Set Mine = ThisWorkbook.Sheets("AllInfo")
myRng = VBA.Array(3, 7, 11, 15, 19, 23, 27)
n = 2
myDir = ThisWorkbook.path & "\CS Tracker\"
fn = Dir(myDir & "*.xls*")
Application.ScreenUpdating = False
Do While fn <> ""
With Workbooks.Open(myDir & fn)
For Each ws In .Sheets
For Each e In myRng
n = n + 1
Mine.Cells(n, 1).Value = _
fn & " : " & ws.Name
For i = 1 To 4
If ws.Cells(e, i).Value > 0 Then
Mine.Cells(n, i + 1).Value = _
ws.Cells(e, i).Value
End If
Next
x = Split("c36,c37,c38,c47,c48,g36,g39,h36,h39,i36,i39,j36,j39,k36,k39", ",")
For i = 0 To UBound(x)
Mine.Cells(n, i + 7).Value = ws.Range(x(i)).Value
Next
For Each s In Split("e3,e7,e11,e15,e19,e24,e27", ",")
Mine.Cells(n, "f").Value = ws.Range(s).Value
Next
Next
.Close False
Next
End With
fn = Dir
Loop
Mine.Columns("a:e").AutoFit
Application.ScreenUpdating = True
Set Mine = Nothing
End Sub
[/vb]
I ran this, e27 shows down column f instead of showing each different cell in the "split" from the script down column f (on the tracker).

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

Sorry it was the same error as before.

[vb]Sub test()
Dim myDir As String, fn As String, ws As Worksheet, myRng
Dim i As Long, e, s, n As Long, Mine As Worksheet, x
Set Mine = ThisWorkbook.Sheets("AllInfo")
myRng = VBA.Array(3, 7, 11, 15, 19, 23, 27)
n = 2
myDir = ThisWorkbook.path & "\CS Tracker\"
fn = Dir(myDir & "*.xls*")
Application.ScreenUpdating = False
Do While fn <> ""
With Workbooks.Open(myDir & fn)
For Each ws In .Sheets
For Each e In myRng
n = n + 1
Mine.Cells(n, 1).Value = _
fn & " : " & ws.Name
For i = 1 To 4
If ws.Cells(e, i).Value > 0 Then
Mine.Cells(n, i + 1).Value = _
ws.Cells(e, i).Value
End If
Next
x = Split("c36,c37,c38,c47,c48,g36,g39,h36,h39,i36,i39,j36,j39,k36,k39", ",")
For i = 0 To UBound(x)
Mine.Cells(n, i + 7).Value = ws.Range(x(i)).Value
Next
For Each s In Split("e3,e7,e11,e15,e19,e23,e27", ",")
n = n + 1
Mine.Cells(n, "f").Value = ws.Range(e).Value
Next
.Close False
Next
End With
fn = Dir
Loop
Mine.Columns("a:e").AutoFit
Application.ScreenUpdating = True
Set Mine = Nothing
End Sub[/vb]

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

When I change that, the compile error comes back again.

"End With without With"

[vb] Next
.Close False
Next
End With
fn = Dir
Loop[/vb]

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

You've made it clear that the statement does not make sense for what I am attempting to do. I understand that it is incorrect, and I am trying to explain what I am trying to do here.

When I remove that line of information, which has been removed from my updated version as of last night before I went to bed knowing that it does not make sense, it totally removes e column information from the tracker.

Column E is not transferring into the tracker
How can I get e3,e7,e11,e15,e19,e23,e27 to appear on the tracker in column f?

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

Right but if you remove those lines and add +7 to the other line, it still does not bring the column E into the tracker. It just skips column F entirely.

Also, I am going to run into a problem- some workbooks have two similar sheets within them, rather than just one. Is there a way to only copy sheets within the workbook that have this same page layout? If not, can we add a message prompt to choose the sheet name within the opened workbook to be copied?

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

If you look at the cells individually in column F, please read the comments and it explains where that information should be going in the "tracking workbook".

Please see attached again. I shortened the comments to be more clear.

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

e3, e7, e11, e15, e19, e23, e27 would fall on column F in the tracker, thats all I want.
I want c36 to start in column G on the tracker and continue out at the top line from there.

I uploaded a copy of the tracker with comments. Hope this helps, I am so sorry for any frustration or confusion.

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

Ok so I tried changing part of the script but that doesn't seem to work. I also tried changing wb.range to wb.cells, but that was no luck

[vb] Next
x = Split("c36,c37,c38,c47,c48,g36,g39,h36,h39,i36,i39,j36,j39,k36,k39", ",")
For i = 0 To UBound(x)
Mine.Cells(n, i + 6).Value = ws.Range(x(i)).Value
Next
n = n + 1
Mine.Cells(n, "f").Value = ws.Range("e3,e7,e11,e15,e19,e24,e27").Value
Next
.Close False
Next
End With
fn = Dir
Loop
Mine.Columns("a:e").AutoFit
Application.ScreenUpdating = True
Set Mine = Nothing
End Sub[/vb]

All that happens differently is that e3 is copied down all 6 rows instead of them being e3= 1st row e7=2nd row e11=3rd row, etc
The other issue I have is that the material bumps down 1 row, but I want everything to stay on the same line in the tracker. How can I do this?

c36 should start at row G on the tracker, not F

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

Works good but, e7 is showing down material used for all the rows in the tracker. Column E shows what material was used and it will differ between lines 3 and 27. Depending on the data entered in columns a:d will decide if there was material ran or not. If material was ran it will show the numbers in columns a:d and column e will have the material that was used entered into it. I zipped the folder I am using for testing, maybe this will assist.

c36 should start on column G in the tracker, not in material used column F.
Material used should be showing down column F
e3 should relfect depending on what is entered in a3:d3
e7 should relfect depending on what is entered in a7:d7
e11 should relfect depending on what is entered in a11:d11
and so forth up to e27

http://www.filesharesite.com/f…0682186CSTracker.zip.html

I wanted to say thank you again, you are such a great help!

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

[vb]Sub test()
Dim myDir As String, fn As String, ws As Worksheet, myRng
Dim i As Long, e, n As Long, Mine As Worksheet, x
Set Mine = ThisWorkbook.Sheets("AllInfo")
myRng = VBA.Array(3, 7, 11, 15, 19, 23, 27)
n = 2
myDir = ThisWorkbook.path & "\CS Tracker\"
fn = Dir(myDir & "*.xls*")
Application.ScreenUpdating = False
Do While fn <> ""
With Workbooks.Open(myDir & fn)
For Each ws In .Sheets
For Each e In myRng
n = n + 1
Mine.Cells(n, 1).Value = _
fn & " : " & ws.Name
For i = 1 To 4
If ws.Cells(e, i).Value > 0 Then
Mine.Cells(n, i + 1).Value = _
ws.Cells(e, i).Value
End If
Next
x = Split("e3,c36,c37,c38,g36,g38,h36,h38,i36,i38,j36,j38,k36,k38", ",")
For i = 0 To UBound(x)
Mine.Cells(n, i + 6).Value = ws.Range(x(i)).Value
Next
n = n + 1
Mine.Cells(n, "f").Value = ws.Range("e7").Value
Next
.Close False
End With
fn = Dir
Loop
Mine.Columns("a:e").AutoFit
Application.ScreenUpdating = True
Set Mine = Nothing
End Sub[/vb]

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

Compile error: End With without With

[vb] Next
n = n + 1
Mine.Cells(n, "f").Value = ws.Range("e7").Value
Next
.Close False
End With
fn = Dir
Loop
Mine.Columns("a:e").AutoFit[/vb]

Thats where I am having the error. What should I do?

Also, not every sheet in the workbook should be copied. Only one or sometimes two sheets, can we specify sheet names?

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

I know you have done so much already, but I was wondering if this could be more specific. For example, column E(3, 7, 11, 15, 19, 23, 27) has data when a value greater than 0 is found in columns B(3, 7, 11, 15, 19, 23, 27) and C(3, 7, 11, 15, 19, 23, 27). Is there a way to copy this onto the same exact line into the tracker when these areas have a value greater than 0?

Thanks so much. Hope your weekend is fun!

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

Works perfectly! Thanks so much

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

"These are cells", is what I should have said. Sorry for any confusion.

This is what I see missing:
WBs | TrackerWB
e3 ---- f3
e7 ---- f4
c36 --- g3
c37 --- h3
c38 --- i3
g36 --- j3
g38 --- k3
h36 --- l3
h38 --- m3
i36 ---- n3
i38 ---- o3
j36 ---- p3
j38 ---- q3
k36 --- r3
k38 --- s3

Does this explain what I mean? I hope I didn't confuse you anymore!

Re: track specific cells in multiple workbooks with multiple sheets, into one main wo

Wow! This is really good!! The only issue I'm having is the rest of the single row items are not being pulled into the tracking workbook. From MatUsed to MarP5 on the tracker is empty. These are single cell items that can be brought across the row. Can we add something small to this, so it brings in those other cells?