Place value from list into specific cells

  • Thread starter Joe_Hunt via OfficeKB.com
  • Start date
J

Joe_Hunt via OfficeKB.com

Hello. I have a list of 270 items with related info. I know how to make the
names from the list create and populate the tab names, and then put the names
of the tabs in a specific cell, but I'd like to put a couple of other pieces
of information from the list in there also. How would I tell Excel for
instance to take everything from column C (except the header, although I can
delete that if necessary) and place it in cell C47 on each worksheet? By that
I mean what's in cell C2 on the list to cell C47 on the first worksheet,
what's in cell C3 on the list to cell C47 on the second worksheet, and so on.
I appreciate any help.
 
R

Rick Rothstein

It is always useful to post any code you have. As for your request, there is
a problem with it... your destination cells overlaps your source cells on at
least one of the sheets. In any event, this macro code should point you in
the right direction (just change the Const statements to match your actual
conditions)...

Sub DistributeColumnData()
Dim X As Long
Dim LastRow As Long
Const FirstRow As Long = 2
Const SourceColumn As String = "C"
Const DestinationCell As String = "C47"
Const SourceSheetName As String = "Sheet1"
With Worksheets(SourceSheetName)
LastRow = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
For X = FirstRow To LastRow
Worksheets(X - 1).Range(DestinationCell).Value = _
.Cells(X, SourceColumn).Value
Next
End With
End Sub
 
J

Joe_Hunt via OfficeKB.com

That's perfect! Thanks!

Rick said:
It is always useful to post any code you have. As for your request, there is
a problem with it... your destination cells overlaps your source cells on at
least one of the sheets. In any event, this macro code should point you in
the right direction (just change the Const statements to match your actual
conditions)...

Sub DistributeColumnData()
Dim X As Long
Dim LastRow As Long
Const FirstRow As Long = 2
Const SourceColumn As String = "C"
Const DestinationCell As String = "C47"
Const SourceSheetName As String = "Sheet1"
With Worksheets(SourceSheetName)
LastRow = .Cells(.Rows.Count, SourceColumn).End(xlUp).Row
For X = FirstRow To LastRow
Worksheets(X - 1).Range(DestinationCell).Value = _
.Cells(X, SourceColumn).Value
Next
End With
End Sub
Hello. I have a list of 270 items with related info. I know how to make
the
Click to expand...
[quoted text clipped - 11 lines]
on.
I appreciate any help.
Click to expand...
Click to expand...
 
Top