Solved

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

Posted on 2011-03-15
8
246 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
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
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!

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

685 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