[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-03-15
8
Medium Priority
?
253 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

830 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