Transmittal Form and Logging

[HE4Giv]

hello..I have a Transmittal in Excel for sending out our
drawings to customers and I want to log all transmittals.
If i have the "To:" (company name) in Cell A3 and the date
in Cell D3,the transmittal number in Cell D4, the project
name in Cell D5 and we start listing the drawings and
descriptions in Cells B7 (drawing number), C7 (description)
and if there is more than one drawing the next drawing
number would fall under Cell B7 (in B8) and its
description would fall under C7 (in Cell C8) How would I
have each transmittal data logged in the same worksheet
under a tab named "Transmittal Logs"?
I did this before to copy data from a particular column
have it transpose the column data into a row (transpose =
false) and use XLdown to find the next available row in
the "transmittal log" worksheet and paste that column into
the next blank row.
my question is how would you tell VBA to select certain
cells (ie:A3,D3,D4,D5,B7,C7) from the transmittal
worksheet and copy them to the transmittal log worksheet
transposing into rows in the next available row? Keeping
in mind that some transmittals may have more than one
drawing which would fill in B8 and C8 and possibly B9 and
C9 if there are three drwaings to send out.
Thank you for your help
(e-mail address removed)

 

[Bernie Deitrick]

HE4,

You could use a macro. For example, if you don't have any cells below
B7 other than those filled with drawing numbers, then you could use
something like this, run while your sheet with the current data is
active:

Sub LogIt()

Dim myCell As Range

With Worksheets("Transmittal Logs")
For Each myCell In Range(Range("B7"), Range("B65536").End(xlUp))
..Range("A65536").End(xlUp)(2).Value = Range("A3").Value
..Range("B65536").End(xlUp)(2).Value = Range("D3").Value
..Range("C65536").End(xlUp)(2).Value = Range("D4").Value
..Range("D65536").End(xlUp)(2).Value = Range("D5").Value
..Range("E65536").End(xlUp)(2).Value = myCell.Value
..Range("F65536").End(xlUp)(2).Value = myCell(1,2).Value
Next myCell
End With

End Sub

HTH,
Bernie
MS Excel MVP

 

[He4Giv]

im confused...I understand some of what you wrote but the
transmittal form where the data is taken from will be on a
worksheet tab named "transmittal Form" and logged using
your "logit" macro onto another worksheet in the same file
named "transmittal Log". Basically the log worksheet will
look something like a Microsoft Access Table with columns
or fields labeled "To" (company name), Date, Transmittal
No., Project, Drawing No., Description, etc. Each data
from the transmittal Form will be copied and transposed
into the log form in rows matching the corresponding
column labels.
Can you tell me what the "A65536" represents in your macro?
I assume A6 is the cell but what is the 5536?

He4

 

[Bernie Deitrick]

HE4,

Run the macro with the "transmittal Form" sheet active.

The A65536 is the last cell in column A. Combined with the
..End(xlUp)(2), it finds the first open cell below your data set.
(That's why there's the requirement to not have anything but drawing
numbers in cells B7 or below.)

Basically, the macro copies your values of interest into the first 6
columns of "transmittal Log", once for each appearance of a drawing
number in cells B7 and down.

HTH,
Bernie
MS Excel MVP