Solved

VB to add contents of once cell to a column on each worksheet

Posted on 2011-03-15
8
243 Views
Last Modified: 2012-05-11
I have a spreadsheet that has many columns full of attributes for one item. The problem is the name of the item isn't on any of the columns. It's actually on cell B:2 of every worksheet. I need the contents of that one cell inserted into a new column for each row that has data. Can this be done using VBA/macros?
0
Comment
Question by:thomasd04
  • 4
  • 3
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
Yes, it can. Is there anywhere in particular you want it, and is there one column that will always have data for each row?
0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
yes, it can be. assuming your data starts at row 4, and you want the result in column D, you can use this code.

Thomas

Sub asdfasdg()
Const lgStartRow As Long = 4
Const strDestCol As String = "D"

Range(Cells(lgStartRow, strDestCol), Cells(Rows.Count, strDestCol).Offset(, -1).End(xlUp).Offset(, 1)).Value = [b2]

End Sub

Open in new window

0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
my code assumes that the column left of where you want to put the name value as values all the way down.
You can change the two constants to adjust your data.

Thomas
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
For example (pending your response)
Sub AddKeyData()
   dim wks as worksheet
   for each wks in activeworkbook.worksheets
with wks
   .columns(1).Insert
   .range("A2", .cells(.rows.count, "B").end(xlUp).offset(0, -1)).Value = .Cells(2, "C").Value
End WIth
Next wks
End sub

Open in new window

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Author Comment

by:thomasd04
Comment Utility
Just to further clarify I added a worksheet example. I need the content of C:1 added to a new ColumnA (with everything then moved to the right).  There is a before and after worksheet describing what is needed. Thanks!
example.xlsx
0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
Sub asdfasdg()
dim sht as worksheet

for each sht in activeworkbook.worksheets
sht.columns(1).insert
sht.range(sht.cells(3,1),sht.cells(rows.count,2).end(xlup).offset(,-1))=[c1]
next

End Sub
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
Based on your sample:
Sub AddKeyData()  
   dim wks as worksheet  
   for each wks in activeworkbook.worksheets  
with wks  
   .columns(1).Insert  
   .range("A3", .cells(.rows.count, "B").end(xlUp).offset(0, -1)).Value = .Cells(1, "D").Value  
End WIth  
Next wks  
End sub

Open in new window


Thomas, yours would use C2 on the activesheet for every sheet I think - you need sht.[D1]
0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
details, details, but yes, you are correct.

Thomas
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now