extracting info

Countryboy69 · Dec 13, 2018

is there a macro that once an external file is open it pulls certain info from it and plugs it in to various sheets?

Macropod · Dec 18, 2018

Kindly read Rule 12 and the advice there on 'bumping': https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
Your incessant bumping serves only to create the impression there is an active, ongoing, discussion, thus potentially deterring others from getting involved.

TotallyConfused · Dec 19, 2018

Hello Countryboy69

I hope this is what you are looking for.

Code:

Option Base 1
Sub TransferData()
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Notes")
    Dim ws5 As Worksheet: Set ws5 = ThisWorkbook.Sheets("Sheet5")
    Dim NotesArray As Variant
    Dim Sheet5Array As Variant
    Dim NotesRng As Variant
    Dim NotesArrayR As Long
    Dim LoopNotesArrayR As Long
    Dim Sheet5ArrayR As Long
With ws1
    NotesArrayR = .Cells(Rows.Count, "A").End(xlUp).Row - 2
    Set NotesRng = .Range("F3", .Range("F3").End(xlDown).End(xlToLeft))
    NotesArray = NotesRng
End With
     ReDim Sheet5Array(NotesArrayR, 2)
     Sheet5ArrayR = 1
     
For LoopNotesArrayR = 1 To NotesArrayR
   If NotesArray(LoopNotesArrayR, 6) > 0 Then
      Sheet5Array(Sheet5ArrayR, 1) = NotesArray(LoopNotesArrayR, 1)
      Sheet5Array(Sheet5ArrayR, 2) = NotesArray(LoopNotesArrayR, 6)
      Sheet5ArrayR = Sheet5ArrayR + 1
   End If
Next

With ws5
    ws5.Activate
    Range("B3").Resize(UBound(Sheet5Array, 1), UBound(Sheet5Array, 2)) = Sheet5Array
End With
End Sub

Sorry it took so long but this has been a learning experience for me.

TotallyConfused

Countryboy69 · Dec 19, 2018

do i need to insert this to the whole workbook or 2 the sheet titled Sheet5?

Countryboy69 · Dec 20, 2018

TotallyConfused said:

Hello Countryboy69

I hope this is what you are looking for.

Code:

Option Base 1
Sub TransferData()
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Notes")
    Dim ws5 As Worksheet: Set ws5 = ThisWorkbook.Sheets("Sheet5")
    Dim NotesArray As Variant
    Dim Sheet5Array As Variant
    Dim NotesRng As Variant
    Dim NotesArrayR As Long
    Dim LoopNotesArrayR As Long
    Dim Sheet5ArrayR As Long
With ws1
    NotesArrayR = .Cells(Rows.Count, "A").End(xlUp).Row - 2
    Set NotesRng = .Range("F3", .Range("F3").End(xlDown).End(xlToLeft))
    NotesArray = NotesRng
End With
     [COLOR=#ff0000]ReDim Sheet5Array(NotesArrayR, 2)[/COLOR]
     Sheet5ArrayR = 1
     
For LoopNotesArrayR = 1 To NotesArrayR
   If NotesArray(LoopNotesArrayR, 6) > 0 Then
      Sheet5Array(Sheet5ArrayR, 1) = NotesArray(LoopNotesArrayR, 1)
      Sheet5Array(Sheet5ArrayR, 2) = NotesArray(LoopNotesArrayR, 6)
      Sheet5ArrayR = Sheet5ArrayR + 1
   End If
Next

With ws5
    ws5.Activate
    Range("B3").Resize(UBound(Sheet5Array, 1), UBound(Sheet5Array, 2)) = Sheet5Array
End With
End Sub

Sorry it took so long but this has been a learning experience for me.

TotallyConfused

i get a error 7 code at the red text part and its pasting the input from A column on notes to the sheet5 in various locations

TotallyConfused · Dec 20, 2018

Countryboy69 said:
i get a error 7 code at the red text part and its pasting the input from A column on notes to the sheet5 in various locations

Hello Countryboy69

I’m sorry you are having problems with the code.

To answer your question in post #23 , I ran the code in the general program module. Is that where you have it? I admit, I didn’t test the code in either of the sheet’s modules, though I wouldn’t think that would make a difference, but it could. If you are trying to run it in a sheet module, delete the code there and place it in the general module. Better yet, use a new test workbook. You don’t want to have the same code in both places as that could cause problems.

When I tested this, I put some text along both sides of the two columns in Sheet5, just to make sure nothing was spilling over into another column. As a final test, after running the code using your numbers, I added a couple of numbers at the end of yours, then ran it again just to make sure the code would accept additional rows. The required numbers fit into those two columns with no problems. I suspect the reason your numbers are scattered is that something went wrong with the way Sheet5Array is set up. I’m not familiar with error message code 7, in fact, I don’t remember ever seeing it. What does the brief text message along with the number say? That may give a clue as to what the problem is.

I noticed you are using a fairly recent version of Office and Windows. I have Office 365 with Windows 10, so I wouldn’t think there should be a compatibility problem, least none that I’ve heard of.

It’s well after midnight here, so I’m going to bed. I won’t be able to get back to this until this evening at the earliest. Maybe someone that is more experienced with VBA than I am will see this before I return and be able to offer a solution.

TotallyConfused

Countryboy69 · Dec 20, 2018

So I added the code in to both the worksheet and modular (yes i deleted it before adding to another) well when i input my info this is what i get

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

[/th][th]

K

[/th][th]

L

[/th][th]

M

[/th][th]

N

[/th][th]

O

[/th][th]

P

[/th][th]

Q

[/th][th]

R

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td=bgcolor:#000000]

JC 12/15/2018

[/td][td=bgcolor:#000000]

MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY

[/td][td=bgcolor:#000000]

INC BOX

[/td][td=bgcolor:#000000]

D-box

[/td][td=bgcolor:#000000]

Disputes

[/td][td=bgcolor:#000000]

D.A.W

[/td][td=bgcolor:#000000]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td=bgcolor:#000000]

Barcode

[/td][td=bgcolor:#000000]

Daughter B/C

[/td][td=bgcolor:#000000]

W.A.D.

[/td][td=bgcolor:#000000]

Full wt.

[/td][td=bgcolor:#000000]

Empty wt.

[/td][td=bgcolor:#000000]

Waste Wt.

[/td][td=bgcolor:#000000]

Types

[/td][td=bgcolor:#000000]

Destination

[/td][td=bgcolor:#000000]

NPC/PSC

[/td][td=bgcolor:#000000]

Notes

[/td][td=bgcolor:#000000]

Full Wt.

[/td][td=bgcolor:#000000]

Empty Wt.

[/td][td=bgcolor:#000000]

Box #

[/td][td=bgcolor:#000000]

Full Wt.

[/td][td=bgcolor:#000000]

Full Wt.

[/td][td=bgcolor:#000000]

Empty Wt.

[/td][td=bgcolor:#000000]

Full Wt.

[/td][td=bgcolor:#000000]

Empty Wt.

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td=bgcolor:#000000]

38031219

[/td][td=bgcolor:#000000]

1250

[/td][td=bgcolor:#000000]

620

[/td][td=bgcolor:#000000]

630

[/td][td=bgcolor:#000000]

B-25

[/td][td=bgcolor:#000000]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td=bgcolor:#000000]

38032525

[/td][td=bgcolor:#000000]

1250

[/td][td=bgcolor:#000000]

620

[/td][td=bgcolor:#000000]

990

[/td][td=bgcolor:#000000]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td=bgcolor:#000000]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: NOTES[/td][/tr][/table]

i need the info in from column J - R to also go to that line and add its self together

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A

[/th][th]

B

[/th][th]

C

[/th][th]

D

[/th][th]

E

[/th][th]

F

[/th][th]

G

[/th][th]

H

[/th][th]

I

[/th][th]

J

[/th][th]

K

[/th][th]

L

[/th][th]

M

[/th][th]

N

[/th][th]

O

[/th][th]

P

[/th][th]

Q

[/th][th]

R

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1

[/td][td=bgcolor:#000000]

Barcode

[/td][td=bgcolor:#000000]

Waste WT

[/td][td=bgcolor:#000000]

40 S/l

[/td][td=bgcolor:#000000]

20' S/l

[/td][td=bgcolor:#000000]

B-25

[/td][td=bgcolor:#000000]

Drum

[/td][td=bgcolor:#000000]

Exelon

[/td][td=bgcolor:#000000]

Other

[/td][td=bgcolor:#FF0000]

[/td][td=bgcolor:#000000]

DAW

[/td][td=bgcolor:#000000]

EX metal

[/td][td=bgcolor:#000000]

Metal

[/td][td=bgcolor:#000000]

Time (in Min)

[/td][td=bgcolor:#000000]

Customer

[/td][td=bgcolor:#000000]

Total to Bin

[/td][td=bgcolor:#000000]

Total to Inc

[/td][td=bgcolor:#000000]

Total to Comp

[/td][td=bgcolor:#000000]

Disputes

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2

[/td][td=bgcolor:#FF0000]

JC 12/15/2018

[/td][td=bgcolor:#FF0000]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3

[/td][td=bgcolor:#000000]

38031219

[/td][td=bgcolor:#000000]

X

[/td][td=bgcolor:#000000]

[/td][td=bgcolor:#FF0000]

[/td][td=bgcolor:#000000]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4

[/td][td=bgcolor:#000000]

[/td][td=bgcolor:#FF0000]

[/td][td=bgcolor:#000000]

630

[/td][td=bgcolor:#000000]

860

[/td][td=bgcolor:#000000]

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5

[/td][td=bgcolor:#000000]

[/td][td=bgcolor:#FF0000]

[/td][td=bgcolor:#000000]

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: DOWNTIME[/td][/tr][/table]

this is what it looks like after my info is added

Countryboy69 · Dec 21, 2018

TotallyConfused said:

Hello Countryboy69

I hope this is what you are looking for.

Code:

Option Base 1
Sub TransferData()
    Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("Notes")
    Dim ws5 As Worksheet: Set ws5 = ThisWorkbook.Sheets("Sheet5")
    Dim NotesArray As Variant
    Dim Sheet5Array As Variant
    Dim NotesRng As Variant
    Dim NotesArrayR As Long
    Dim LoopNotesArrayR As Long
    Dim Sheet5ArrayR As Long
With ws1
    NotesArrayR = .Cells(Rows.Count, "A").End(xlUp).Row - 2
    Set NotesRng = .Range("F3", .Range("F3").End(xlDown).End(xlToLeft))
    NotesArray = NotesRng
End With
     ReDim Sheet5Array(NotesArrayR, 2)
     Sheet5ArrayR = 1
     
For LoopNotesArrayR = 1 To NotesArrayR
   If NotesArray(LoopNotesArrayR, 6) > 0 Then
      Sheet5Array(Sheet5ArrayR, 1) = NotesArray(LoopNotesArrayR, 1)
      Sheet5Array(Sheet5ArrayR, 2) = NotesArray(LoopNotesArrayR, 6)
      Sheet5ArrayR = Sheet5ArrayR + 1
   End If
Next

With ws5
    ws5.Activate
    [COLOR=#ff0000]Range("B3").Resize(UBound(Sheet5Array, 1), UBound(Sheet5Array, 2)) = Sheet5Array[/COLOR]
End With
End Sub

Sorry it took so long but this has been a learning experience for me.

TotallyConfused

OK so now on the red text section i get Run-time error'1004': Application-defined or object-defined error

Countryboy69 · Dec 23, 2018

Countryboy69 · Dec 25, 2018

extracting info

Countryboy69

Board Regular

Macropod

Retired Moderator

Excel Facts

TotallyConfused

Board Regular

Countryboy69

Board Regular

Countryboy69

Board Regular

TotallyConfused

Board Regular

Countryboy69

Board Regular

Countryboy69

Board Regular

Countryboy69

Board Regular

Countryboy69

Board Regular

Similar threads

Forum statistics

Share this page

extracting info

Board Regular

Retired Moderator

Excel Facts

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock