Transfer Lists to Forms

B

beeawwb

Good afternoon all,

I'm currently working on transcribing a list of files we have archive
into an Excel spreadsheet. Before anybody asks I'm not using Acces
(which would be my first choice) because I'm the only one with i
installed in our group.

What I have is this. A large list of file archived which contains

(Column) A: Box / Barcode ID
B: Name On File
C: Destruction Date (Reference Only)
D: Date Of Loss (Reference Only).

This is the first sheet.
On a second sheet, I have made an exact duplicate of the faxes I woul
send off to the storage company if I need a file retrieved. What I wan
to do is this.

Highlight a file (Column B) the click a button, and have
Information in Column A go to the first Empty row (There are 20 rows o
the Fax)
Information in Column B go to the second cell in this Row
Information in Column B go to the third cell in this Row with the word
"All Files for " preceeding. (So, "All Files For " & [Cell])

Unfortunatly, I don't know how to A) transfer only the current cell
and B) how to reference the next Empty row in a block.

I assume this will all be VBA code, and there are 10000+ files (pity m
fingers typing).

Edit: I forgot to mention, that the cells on the fax are merged cells
So, where A563 (Barcode) would transfer, it goes into [B/C]29 (Merged)
and B563 goes into [D/E]29 and [F/G/H]29

Any ideas? Your help is greatly appreciated.

Have a good day,

Robert
 
D

Dave Peterson

I got confuse at what column went to which column (maybe a typo or two in you
post???).

But this should be easy to modify:

Option Explicit
Sub TransferData()
Dim wks As Worksheet
Dim FaxWks As Worksheet
Dim NextRow As Long
Dim CurRow As Long

Set wks = ActiveSheet
Set FaxWks = Worksheets("FAX")

With FaxWks
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If NextRow > 30 Then 'what's the last row in the form?
MsgBox "Fax sheet filled up!" & vbLf & _
"Please clean it up before continuing."
Exit Sub
End If
End With

CurRow = ActiveCell.Row
With wks
FaxWks.Cells(NextRow, "B").Value = .Cells(CurRow, "A").Value
FaxWks.Cells(NextRow, "D").Value _
= "All Files For " & .Cells(CurRow, "c").Value
FaxWks.Cells(NextRow, "C").Value = .Cells(CurRow, "B").Value
End With
End Sub

Put this code in a general module. I'd put a button from the Forms toolbar in
row 1. Window|Freeze Panes so row 1 is always visible.

The select any cell in the row you want transferred and click the button.



beeawwb < said:
Good afternoon all,

I'm currently working on transcribing a list of files we have archived
into an Excel spreadsheet. Before anybody asks I'm not using Access
(which would be my first choice) because I'm the only one with it
installed in our group.

What I have is this. A large list of file archived which contains

(Column) A: Box / Barcode ID
B: Name On File
C: Destruction Date (Reference Only)
D: Date Of Loss (Reference Only).

This is the first sheet.
On a second sheet, I have made an exact duplicate of the faxes I would
send off to the storage company if I need a file retrieved. What I want
to do is this.

Highlight a file (Column B) the click a button, and have
Information in Column A go to the first Empty row (There are 20 rows on
the Fax)
Information in Column B go to the second cell in this Row
Information in Column B go to the third cell in this Row with the words
"All Files for " preceeding. (So, "All Files For " & [Cell])

Unfortunatly, I don't know how to A) transfer only the current cells
and B) how to reference the next Empty row in a block.

I assume this will all be VBA code, and there are 10000+ files (pity my
fingers typing).

Edit: I forgot to mention, that the cells on the fax are merged cells.
So, where A563 (Barcode) would transfer, it goes into [B/C]29 (Merged),
and B563 goes into [D/E]29 and [F/G/H]29

Any ideas? Your help is greatly appreciated.

Have a good day,

Robert.
Click to expand...
 
B

beeawwb

I'll answer the second question first.

Not using Jet Query or MS Query because... well, don't know how to a
this stage (although I'm sure I could learn) I think I'll stick wit
Excel, so that it's easier on everybody.

Secondly,

Dave's code...

Sub TransferData()
Dim wks As Worksheet
Dim FaxWks As Worksheet
Dim NextRow As Long
Dim CurRow As Long

Set wks = ActiveSheet
Set FaxWks = Worksheets("Request")

With FaxWks
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If NextRow > 48 Then 'what's the last row in the form?
MsgBox "Fax sheet filled up!" & vbLf & _
"Please clean it up before continuing."
Exit Sub
End If
End With

CurRow = ActiveCell.Row
With wks
FaxWks.Cells(NextRow, "B").Value = .Cells(CurRow, "A").Value
FaxWks.Cells(NextRow, "D").Value _
= "All Files For " & .Cells(CurRow, "c").Value
FaxWks.Cells(NextRow, "C").Value = .Cells(CurRow, "B").Value
End With
End Sub



Regardless of what I put for "NextRow" maximum value (the sheet ends a
row 48) I get the Clean Up message.

Now let's see if I can clear up some of the confusion with what'
where.

Worksheet LIST
Sample Data
A: Box Number B: Contents C: Destroy D: DOL
C5501344258 Aronis, M "Dec 2007" (No DOL)

Worksheet Request

B/C (Merged) D/E (Merged) F/G/H (Merged)
Carton Barcode - File Barcode - Description


Carton Barcode = Box Number
File Barcode = Contents
Description = "All Files For " & Contents

"Request" (The Fax) starts at Row 29 ends at row 48.

Many thanks for your assistance,

-Bo
 
B

beeawwb

Addendum:

I fixed it. I was stepping through the code, and I noticed there wer
some other bits I needed to change. Made some minor modification
regarding to blank rows (row 27 is the first blank row, but row 29 i
the first row of the Fax).

But, I think I've got it figured out now, thank you VERY much for you
help. :D

-Bo
 
D

Dave Peterson

Glad you got it working!

beeawwb < said:
Addendum:

I fixed it. I was stepping through the code, and I noticed there were
some other bits I needed to change. Made some minor modifications
regarding to blank rows (row 27 is the first blank row, but row 29 is
the first row of the Fax).

But, I think I've got it figured out now, thank you VERY much for your
help. :D

-Bob
Click to expand...
 
Top