Solved

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

Posted on 2011-03-15
8
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139930
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
ID: 35139952
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
ID: 35139960
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35139967
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
 
LVL 3

Author Comment

by:thomasd04
ID: 35140056
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
ID: 35140091
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
ID: 35140151
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
ID: 35140193
details, details, but yes, you are correct.

Thomas
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

738 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