VBA code to Add data to exsiting worksheet

Hi,

I got this code from the net, but I wish to modify it so that it ca
add data to the exsiting Master sheet whenever a new worksheet has bee
added to the exsiting workbook which already contain other sheets. 
also try with the Function LastRow but it didn't give the results 
want. :confused:  I hope some experts can please kindly help to take 
look to modify the codes. Any similar ideas also accepted.

Thankyou very much for any guides.

The codes are as below:

Option Explicit

Sub CopyRange()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
If sh.UsedRange.Count > 1 Then
Last = LastRow(DestSh)
sh.Range("A1:C5").Copy DestSh.Cells(Last + 1, 1)
End If
End If
Next
Application.ScreenUpdating = True
End Sub

Sub CopyRangeValues()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
If SheetExists("Master") = True Then
MsgBox "The sheet Master already exist"
Exit Sub
End If
Application.ScreenUpdating = False
Set DestSh = Worksheets.Add
DestSh.Name = "Master"
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> DestSh.Name Then
If sh.UsedRange.Count > 1 Then
Last = LastRow(DestSh)
With sh.Range("A1:C5")
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With
End If
End If
Next
Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

Function SheetExists(SName As String, _
Optional ByVal WB As Workbook) As Boolean
On Error Resume Next
If WB Is Nothing Then Set WB = ThisWorkbook
SheetExists = CBool(Len(Sheets(SName).Name))
End Functio

--
jonesaa0
-----------------------------------------------------------------------
jonesaa05's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2802
View this thread: http://www.excelforum.com/showthread.php?threadid=47528

0
10/12/2005 3:43:00 AM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
421 Views

Similar Articles

[PageSpeed] 21

It looks like the CopyRange and CopyRangeValues subroutines both do about the
same thing.  Each combines all the existing data on all the other worksheets and
puts it onto a Worksheet called master.

But the CopyRange version copies the cells (including formulas and formats),
while the copyrangevalues just takes the values from each sheet (avoiding
formulas and formats).

So pick the one you want and delete the other.

Then, since they each rebuild the master worksheet, you can just delete the
existing master worksheet first.  Then run the macro.

Try this against a copy of your workbook--just to make sure it does what you
want.

===
Knowing which sheet was new and when to copy that data may be more difficult
than just recreating the master worksheet.

But as an alternative, you could just copy the range to the bottom of the master
worksheet when you want.

If you record a macro when you try this, your code could be very close to done.

Post back if you need help making it more general.

jonesaa05 wrote:
> 
> Hi,
> 
> I got this code from the net, but I wish to modify it so that it can
> add data to the exsiting Master sheet whenever a new worksheet has been
> added to the exsiting workbook which already contain other sheets. I
> also try with the Function LastRow but it didn't give the results I
> want. :confused:  I hope some experts can please kindly help to take a
> look to modify the codes. Any similar ideas also accepted.
> 
> Thankyou very much for any guides.
> 
> The codes are as below:
> 
> Option Explicit
> 
> Sub CopyRange()
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim Last As Long
> If SheetExists("Master") = True Then
> MsgBox "The sheet Master already exist"
> Exit Sub
> End If
> Application.ScreenUpdating = False
> Set DestSh = Worksheets.Add
> DestSh.Name = "Master"
> For Each sh In ThisWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> If sh.UsedRange.Count > 1 Then
> Last = LastRow(DestSh)
> sh.Range("A1:C5").Copy DestSh.Cells(Last + 1, 1)
> End If
> End If
> Next
> Application.ScreenUpdating = True
> End Sub
> 
> Sub CopyRangeValues()
> Dim sh As Worksheet
> Dim DestSh As Worksheet
> Dim Last As Long
> If SheetExists("Master") = True Then
> MsgBox "The sheet Master already exist"
> Exit Sub
> End If
> Application.ScreenUpdating = False
> Set DestSh = Worksheets.Add
> DestSh.Name = "Master"
> For Each sh In ThisWorkbook.Worksheets
> If sh.Name <> DestSh.Name Then
> If sh.UsedRange.Count > 1 Then
> Last = LastRow(DestSh)
> With sh.Range("A1:C5")
> DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
> Columns.Count).Value = .Value
> End With
> End If
> End If
> Next
> Application.ScreenUpdating = True
> End Sub
> 
> Function LastRow(sh As Worksheet)
> On Error Resume Next
> LastRow = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Row
> On Error GoTo 0
> End Function
> 
> Function Lastcol(sh As Worksheet)
> On Error Resume Next
> Lastcol = sh.Cells.Find(What:="*", _
> After:=sh.Range("A1"), _
> Lookat:=xlPart, _
> LookIn:=xlFormulas, _
> SearchOrder:=xlByColumns, _
> SearchDirection:=xlPrevious, _
> MatchCase:=False).Column
> On Error GoTo 0
> End Function
> 
> Function SheetExists(SName As String, _
> Optional ByVal WB As Workbook) As Boolean
> On Error Resume Next
> If WB Is Nothing Then Set WB = ThisWorkbook
> SheetExists = CBool(Len(Sheets(SName).Name))
> End Function
> 
> --
> jonesaa05
> ------------------------------------------------------------------------
> jonesaa05's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28021
> View this thread: http://www.excelforum.com/showthread.php?threadid=475287

-- 

Dave Peterson
0
petersod (12005)
10/12/2005 2:03:53 PM
First, thanks Dave for your tips.

Now, I try to run the code to copy the same range (say A1:L10)from 
workbooks. The code suppose to copy the range values from first shee
in each workbook. It does copy the range I needed into the first shee
but only values from workbook 1 and no values from workbook2. Seem
that somthing to do with the looping. I am now seeking for help to d
something with the code so that once the macro is run, the same range
for all the workbook can be copied into the sheets accordingly.

Is this possible to do? Thankyou very much in advance.

Regards,
am

--
jonesaa0
-----------------------------------------------------------------------
jonesaa05's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2802
View this thread: http://www.excelforum.com/showthread.php?threadid=47528

0
10/13/2005 8:58:13 AM
The code you posted doesn't copy from different workbooks.  It copies from
different worksheets within the same workbook.

Ron has lots of sample code on that page.  I'm not sure which one you're using. 
You may want to post your efforts once again.

jonesaa05 wrote:
> 
> First, thanks Dave for your tips.
> 
> Now, I try to run the code to copy the same range (say A1:L10)from 2
> workbooks. The code suppose to copy the range values from first sheet
> in each workbook. It does copy the range I needed into the first sheet
> but only values from workbook 1 and no values from workbook2. Seems
> that somthing to do with the looping. I am now seeking for help to do
> something with the code so that once the macro is run, the same ranges
> for all the workbook can be copied into the sheets accordingly.
> 
> Is this possible to do? Thankyou very much in advance.
> 
> Regards,
> amy
> 
> --
> jonesaa05
> ------------------------------------------------------------------------
> jonesaa05's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28021
> View this thread: http://www.excelforum.com/showthread.php?threadid=475287

-- 

Dave Peterson
0
petersod (12005)
10/13/2005 12:35:23 PM
Hi, Dave

I post the code which I use to copy values from at least 2 workbook in
the same folder. Only the values from 1 workbook are correctly copied.
The others give 0 values. Please help me to have a look whether the is
the code problems. One more thing, which line of code to be changed if
I don't want to copy to the first sheet. Sorry, I am still novice to
VBA coding things.

Thanks in advanced,
amy

Sub CopyRangeValues()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim a As Long
Application.ScreenUpdating = False
With Application.FileSearch
..NewSearch
..LookIn = "D:\Data\"
..SearchSubFolders = False
..FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
Set basebook = ThisWorkbook
rnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Range("a1:k10")
a = sourceRange.Rows.Count
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
1). _
Resize(.Rows.Count,
..Columns.Count)
End With
destrange.Value = sourceRange.Value
mybook.Close
rnum = i * a + 1  'What does this means?? :confused:  

Next i
End If
End With
Application.ScreenUpdating = True
End Sub


-- 
jonesaa05
------------------------------------------------------------------------
jonesaa05's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28021
View this thread: http://www.excelforum.com/showthread.php?threadid=475287

0
10/14/2005 1:45:21 AM
This is the line that says where to copy the data from:

     Set sourceRange = mybook.Worksheets(1).Range("a1:k10")

That worksheets(1) means the left most worksheet when you're looking at the
worksheet tabs.

If the name of the worksheet is always the same (say "Sheet99"), you could use:

     Set sourceRange = mybook.Worksheets("Sheet99").Range("a1:k10")

Do you know the name of the sheets that should be copied?  And are they always
that same name?

======
As for this portion:
rnum = i * a + 1  'What does this means?? :confused:

Ron's sample code always uses A1:K10.  That's 10 rows of data.

He also makes it easier for you to customize his code.  If your range is
different, you could use:

Set sourceRange = mybook.Worksheets("Sheet99").Range("a1:G100")
That would take a 100 rows of values (A:G)

The next line determines how many rows per "copy".
a = sourceRange.Rows.Count
In Ron's sample, "a" will be 10.  It's the number of rows in A1:K10.

rnum = i * a + 1  'What does this means?? :confused:

In this line, rnum is the next row that's gonna get the values from the next
workbook that's opened.

i represents a counter of which file you're on.  

Right after a set of data is populated, Ron says to take the number of rows per
"copy" and multiply it by the number of files that have been processed.  Then
add 1.

So right after the first workbook's A1:K10 is put into the new worksheet, Ron's
code will evaluate to:

rnum = i * a + 1
rnum = 1 * 10 + 1
rnum = 10 + 1
rnum = 11

So the next time through, Ron will start in row 11.

Then the next time, it'll be 21 (2*10+1), then 31 (3*10+1).

All this works because Ron is taking the values from 10 rows each time.

Ps.  Ron isn't really copy|pasting.  He's just assigning values.  That's what
this line does:

destrange.Value = sourceRange.Value

========
So the code is probably working ok.  But you have to make sure your situation
fits this code.

#1.  Same worksheet name in each workbook
#2.  Same range "copied" to the new worksheet.

If these two aren't true, then Ron's code would have to be modified.  But you'll
have to say what you really want.



jonesaa05 wrote:
> 
> Hi, Dave
> 
> I post the code which I use to copy values from at least 2 workbook in
> the same folder. Only the values from 1 workbook are correctly copied.
> The others give 0 values. Please help me to have a look whether the is
> the code problems. One more thing, which line of code to be changed if
> I don't want to copy to the first sheet. Sorry, I am still novice to
> VBA coding things.
> 
> Thanks in advanced,
> amy
> 
> Sub CopyRangeValues()
> Dim basebook As Workbook
> Dim mybook As Workbook
> Dim sourceRange As Range
> Dim destrange As Range
> Dim rnum As Long
> Dim i As Long
> Dim a As Long
> Application.ScreenUpdating = False
> With Application.FileSearch
> NewSearch
> LookIn = "D:\Data\"
> SearchSubFolders = False
> FileType = msoFileTypeExcelWorkbooks
> If .Execute() > 0 Then
> Set basebook = ThisWorkbook
> rnum = 1
> For i = 1 To .FoundFiles.Count
> Set mybook = Workbooks.Open(.FoundFiles(i))
> Set sourceRange = mybook.Worksheets(1).Range("a1:k10")
> a = sourceRange.Rows.Count
> With sourceRange
> Set destrange = basebook.Worksheets(1).Cells(rnum,
> 1). _
> Resize(.Rows.Count,
> Columns.Count)
> End With
> destrange.Value = sourceRange.Value
> mybook.Close
> rnum = i * a + 1  'What does this means?? :confused:
> 
> Next i
> End If
> End With
> Application.ScreenUpdating = True
> End Sub
> 
> --
> jonesaa05
> ------------------------------------------------------------------------
> jonesaa05's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28021
> View this thread: http://www.excelforum.com/showthread.php?threadid=475287

-- 

Dave Peterson
0
petersod (12005)
10/14/2005 2:58:46 AM
Dear Dave,

First,  if to make the code work ok, I can manually make the same
worksheet name in each workbook. But, I also need the same range of
every sheet to be"copied " to the new worksheet. So, the code would
have to be modified  like what you said. Correct me if I am wrong.

My question is : How does it can be done?


Thanks,
amy


-- 
jonesaa05
------------------------------------------------------------------------
jonesaa05's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28021
View this thread: http://www.excelforum.com/showthread.php?threadid=475287

0
10/14/2005 3:31:18 AM
That's correct.

I would think that the only line you'll be changing in the code is this:

Set sourceRange = mybook.Worksheets("Sheet99").Range("a1:k10")

Change sheet99 to whatever you call all those sheets and change a1:k10 to
whatever range you want copied.

jonesaa05 wrote:
> 
> Dear Dave,
> 
> First,  if to make the code work ok, I can manually make the same
> worksheet name in each workbook. But, I also need the same range of
> every sheet to be"copied " to the new worksheet. So, the code would
> have to be modified  like what you said. Correct me if I am wrong.
> 
> My question is : How does it can be done?
> 
> Thanks,
> amy
> 
> --
> jonesaa05
> ------------------------------------------------------------------------
> jonesaa05's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28021
> View this thread: http://www.excelforum.com/showthread.php?threadid=475287

-- 

Dave Peterson
0
petersod (12005)
10/14/2005 12:20:53 PM
Reply:

Similar Artilces:

referencing to worksheet names in macro for each new worksheet inserted
Hi I created a code to insert new worksheets and rename them according t values on the new worksheet itself. Say in Cell D1, i have th worksheet name. My question is when i want to refer to this worksheet in subsequen coding, how should i code it? For eg, How should i write the ???? for Sheets("????").select? Would creatin the a variable to store the names help? Thanks in advance Ken -- Message posted from http://www.ExcelForum.com After inserting your new worksheet set it's name equal to a variable. For example SHEETS.ADD VWORKSHEET = ACTIVESHEET.NAME This method ...

How do I modify an existing worksheet to remove columns & contents
I am working with a very large spreadsheet and want to modify it using only certain columns and data in order to keep from re-doing the entire worksheet. ANybody out there in cyberspace got suggestions or good reference sources I can use? Gil There are just too many scenarios to ponder without getting some more details on what you wish to do. Please be a little more specific in your description. Gord Dibben Excel MVP On Wed, 9 Feb 2005 18:15:06 -0800, "Gil Gray" <Gil Gray@discussions.microsoft.com> wrote: >I am working with a very large spreadsheet and want to m...

VB code for Macro
I have set up a rule on my InBox to check for specific words and move emails to my Work folder. Now I review emials in my Work folder and drag and drop them into 1 of 4 folders based on a number 1-4. After clicking on the folder, I need to perform the following on each of the four folders: Click on first email in the folder Clt+A (to select all the emails in the folder) Ctl+C (to copy) Drag the selections to a folder name HH Click #_Button (customized button set to send an email) Ctl+v (to paste the contents in the body of the email) Click Send Steps without the comments: Click folde...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

Pivot Tables & changing data
Hi - I have never used pivot tables in Excel before so hopefully what I am going to ask is possible and not too complicated for me.... :o) I am working with Excel 2003. I have a pivot table already set up and the information is pulling data from a row titled "sum of Subscriber". I added new data in a new column from the main spreadsheet and I would like to pull the data from there. Its titled "Adj Subscriber". Is it possible to switch it? If so, how? Thanks, Anna Marie Anna wrote: > Hi - I have never used pivot tables in Excel before so hopefully what I am &...

Process all cells in a (user) selection
I am using this code to give me cell by cell access to a code defined selection; Dim aCell As Range For Each aCell In Sheet4.Range("A4:A34") ' Do Stuff Here Next aCell which processes every cell in the A4:A34 Range How do I do the same thing for a selection drawn by the user before pressing my 'Process' button ? Thanks On 05 May 2010 11:10:21 GMT, Isis <isissoft@NOSPAMbtinternet.com> wrote: >I am using this code to give me cell by cell access to a code defined >selection; > >Dim aCell As Range >For Each aCell In S...

how to edit my x-axis data on a line graph
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel my x axis data on my graph is just showing numbers 1 2 3 4 5 6 7 8 9 10 11.... and it is suppose to show the years. I can't figure out how to change these values ...

No email stationery when through code
Hi I am creating email via code from access using below; Dim objOutlook As Outlook.Application Dim objOutlookMsg As Outlook.MailItem Set objOutlook = CreateObject("Outlook.Application") Set objOutlookMsg = objOutlook.CreateItem(olMailItem) objOutlookMsg.To = "recepient@mydomain.com" objOutlookMsg.Subject = "Email Subject" objOutlookMsg.Body = "Dear abc..." objOutlookMsg.Display The problem is that the default signature that is set in outlook doe snot come up in the email. How can I fix it? Thanks Regards Am Sun, 17 Sep 2006 21:11:45 +0100 schri...

how to add singnature
"prasad" <prasad@discussions.microsoft.com> wrote in message news:5107796D-4E02-45AB-9816-47670410FA63@microsoft.com... > Please use the message body to provide information that will be invaluable in getting help. Things like: Version of Outlook. Type of mail server (exchange, pop/imap, hotmail). What you're trying to do (because, frankly, I could answer your question with 'You go to the end of your message and type it in' and be correct). What you've tried to do and how it failed. -- f.h. ...

How to view the code for excel built-in functions?
Is it possible? -For example the function PMT(). thanks. No, the code is compiled, so it would likely be less than useful anyway. About the best you can do is check out the equations used in Help (see "PV"). In article <OSU3OXOBGHA.1676@TK2MSFTNGP09.phx.gbl>, "serdar" <s@s.com> wrote: > Is it possible? -For example the function PMT(). > thanks. ...

VBA- Application.Wait?
Does PowerPoint not have "Application.Wait" like Excel? Is there another way to pause code for a few seconds in PPT 2003/2007 while a small bat file runs? -Mel On 3/10/10 2:29 AM, Mel wrote: > Does PowerPoint not have "Application.Wait" like Excel? Is there > another way to pause code for a few seconds in PPT 2003/2007 while a > small bat file runs? > > -Mel From Example 8.4 on my Web site (http://www.PowerfulPowerPoint.com/): Sub Wait() waitTime = 5 Start = Timer While Timer < Start + waitTime DoEvents ...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

How do I add multiple times together
Hi does anyone know how I can add multiple times togther and get the anser in hours and minutes. I have formatted the cell for time however when I atosum I keep getting an answer that is incorrect. Any help? Thanks D Maybe it was just a formatting problem. Try a custom format of: [hh]:mm Playhouse pm wrote: > > Hi does anyone know how I can add multiple times togther and get the anser in > hours and minutes. I have formatted the cell for time however when I atosum I > keep getting an answer that is incorrect. Any help? > Thanks > D -- Dave Peterson ...

will not let me add my hot mail e-mail
how do I add my msn hotmail email to out look "stephen2428" <stephen2428@discussions.microsoft.com> wrote in message news:101C02C3-519B-4B3C-8D8B-FABDCDEB9DE7@microsoft.com > how do I add my msn hotmail email to out look You can't unless you have a paid-for Premium Account. "stephen2428" <stephen2428@discussions.microsoft.com> wrote in message news:101C02C3-519B-4B3C-8D8B-FABDCDEB9DE7@microsoft.com... > how do I add my msn hotmail email to out look And we are supposed to guess as to WHICH version of Outlook that you use? HTTPmail got added fo...

Remove Add-Ins from Tools Menu
I installed an Add-In, used it an then uninstalled it. The Add-In still shows on the Tools menu. How do you get it off the menu? I exited and restarted the the program several times and the add-in is unchecked. Excel 2000, 9.04402 SR-1 Karl Does this add-in have a name? Some third-party applications are noted for sticking files in XLSTART. Visio comes to mind. Gord Dibben Excel MVP On Mon, 12 Jan 2004 16:31:17 GMT, "Karl Irvin" <88karl3200@comcast.net> wrote: >I installed an Add-In, used it an then uninstalled it. The Add-In still >shows on the Tools menu. How ...

Add a horizontal line to a graph but not to the legend
I want to be able to add horizontal lines to my chart, in this case at 4% and 6% on the y-axis. The solutions I've seen so far suggest to add a series at these points. The problem is that when I add a seires, my legend shows the new series when I don't really want them to. Is there a really simple way that I've overlooked so that I can just add a line at one y-value and all the x values on the graph and not have it appearing in the legend? Thanks I have just tested inserting an extra series in both xl2002 and xl2007 and if you select the legend and then select the indiv...

Compressed Outlined Data Copy
I need to copy only the data that is compressed in Grouped rows and not all the rows when they are expanded. When I copy and paste the compressed data, I get all the data that is within the group, and not just the compressed data. For example: I need just the following (which is compressed): Ship $ Line $5,278.00 CYP Total $133,122.00 TI Total $34,660,135.00 Vic Total $34,798,535.00 Grand Total when I copy it (D2:E17, but only showing 5 lines because of the compression), it pastes 16 lines: Ship $ Line $123.00 CYP $44.00 CYP $4,444.00 CYP $667.00 CYP $5,278.00 CYP Total $123,124.00 TI $...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

extraction code from celd
Hi :) I have a Excel problem :confused: MY QUESTION IS: if A5 = "JhoN FreD SmitH ChonG then A6 = "JNFDSHCG" I would like have got solution ;) please !!! thank you ver much nando4000@latinmail.co ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com You could create a User Defined Function: '=============================== Function GetCode(rng As Range) As String Dim i As Integer Dim str As String i = Len(rng.Value) For i = 1 To i str = Mid(rn...

Linking Drop-down list to worksheets
I need 20 dropdown list on the worksheet. some of drop-down lists has the same values. I need a list thats has one set of names and worksheet has another set of names. Here is a example want I need. LIST names Worksheet names Red - Black 770rb Black-White 770bw My list will have 15 items in each list. When user click on the color it would take them to that worksheet. What is best way to do this? ...

Retreiving data
We move mailbox on a new server but somethimes we receive the following message: "outlook is retrieving data from the OLD exchange server" Can anyone help me -- Dambo On Thu, 1 Feb 2007 01:21:01 -0800, Damb0 <Damb0@discussions.microsoft.com> wrote: >We move mailbox on a new server but somethimes we receive the following >message: "outlook is retrieving data from the OLD exchange server" > >Can anyone help me What steps did you do to decommission the old server? If you give us an idea of the version of the old and new ones we can post the right help ...

compare two columns with different ranges in two worksheets
I need to compare two columns of data in two different worksheets and display a third one. Here it is an example: -(worksheet1!A1:A10), (worksheet1!B1:B10) and (whorksheet2!C1:C25) -this is my query, if C5 is already in (A1:A10) I want to display B5 in worksheet2!D5 I think it is tricky because you need to identity which row in the A1:A10 is equal to C5 to display B5 and the range are different. you could save my day chris90 In worksheet2!D1: =if(isna(vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)), "", vlookup(C1, worksheet1!$A$1:$B$10, 2, 0)) HTH Kostis Vezerides brilliant, ma...

Help Required with Macro to Manipulate Data
I have a spreadsheet(s) with the following data on it (this is imported from a text file) A B C D 1 Branch Date Total 2 4501 030204 29 3 4 4501 030204 14 5 6 4502 030204 331 7 8 4502 030204 52 9 10 4503 030204 54 11 12 4503 030204 85 Hi Alay and what do you want to achieve :-) -- Regards Frank Kabel Frankfurt, Germany "Alan T >" <<Alan.T.12jthe@excelforum-nospam.com> schrieb im Newsbeitrag news:Alan.T.12jthe@excelforum-nospam.com... > I have a spreadsheet(s) with the following data on it (this is imported ...

Save Worksheet As HTML Page
Hi there I recorded a macro to do a simple html export that is assigned to a button. This works fine. The VBA code is Sub Macro1() ' ' Macro1 Macro ' Macro recorded 9/02/2006 by rulebr ' ' With ActiveWorkbook.PublishObjects("Brady Standard Quote 7.2.06_23158") .HtmlType = xlHtmlStatic .Publish (False) End With End Sub Id like to add a few more in the file name it saves it to.Through vba code is there a way to make the file that is save with the following file name format. QuoteNo_1000_cell(D10)_todaysdate.htm So an example would...