?
Solved

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

Posted on 2011-03-15
8
Medium Priority
?
249 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

741 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