Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

copying data in various worksheets to one other

  Asked By: Sean    Date: Aug 15    Category: MS Office    Views: 1034
  

I've reached a stage when I can't get any further with some data
processing and could do with some help please!

My data arrives in a single workbook but will have varying numbers of
worksheets.
I'm not good at macros but have managed to write one by using the
record macro thingy and cribbing here and there. Messy but it works.

So, for all worksheets, I can unformat and strip out unwanted blank
columns and rows, add in some necessary text. I need next to copy the
data from each worksheet within the workbook into one new blank
worksheet. I could add a new sheet Ok but the copying is the prob.

The data in the worksheets I want to copy is always in the same
columns A:E but the number of rows varies. Also of course when I do
get to copy to a master sheet the row length will vary with each
addition to it.

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Jay Richards     Answered On: Aug 15

Some more recording may fit your needs here.

Try recording going to the last row of the *sheet* column A... Or if you
know there are going to be less than 1000 rows, Cell A1500.. Just some cell
past the last row you expect.

Still in the recorder, hit End and then Up Arrow. This should take you to
the last row of your data.

Now move over to Col E.

Now Press Ctrl + Shift + Home.

This should select all the data  you want copied.

Go to the sheet you want to paste into and repeat the going to the last row
of data.

Now move down a row and paste.

*Now* you can stop recording and have a look at the code.

It's not elegant but fairly generic with all those Ends and Homes and stuff.

If you're still stuck then *do* get back and we';; see what can be done.

 
Answer #2    Answered By: Wade Jordan     Answered On: Aug 15

There are a few strategies to address this endeavor....
I am calling your data  file 'Source' and the 'Target' is where your data gets
pasted into.
A few questions for clearity:
1. Is the 'Source' file always the same name?
2. Are the 'Source' worksheets  (tabs) always the same number or name?

I was thinking of this:
Have the 'Target' file (or sheet) have the logic to select the 'Source' file &
bring the data into the 'Target' file. It's just one perspective on a solution
option!

 
Answer #3    Answered By: Roderick King     Answered On: Aug 15

I seem to have stumbled and staggered my way to a solution using this
bit of code

Dim Item As Worksheet

For Each Item In ActiveWorkbook.Sheets
Item.Select
ActiveSheet.UsedRange.Select
Selection.Copy
Sheets("Master").[A65536].End(xlUp)(2).PasteSpecial
Paste:=xlValues

Next Item

I'll carry on testing and come back here if I run into snags.
Meanwhile if any of you see any potential disasters that might occur
do give a shout, I don't have much of a clue with any of this!

If it continues to work I'll be back with another query because I have
next to work on the block of data  that I get from this stage of the
processing.

 
Answer #4    Answered By: Fatih Farooq     Answered On: Aug 15

You can also use the following for your target starting point, instead of going
from the bottom, upward:

vTargetStart=Sheets("Master").Range("A1").CurrentRegion.Rows.Count
Sheets("Master").Range("A1").offset(vTargetStart,0).Select
Selection.PasteSpecial Paste:=xlValues

 
Answer #5    Answered By: Isaac Evans     Answered On: Aug 15

further to my recent post I'n now completely dazed and confused in
macroland. Believe me, I've tried but programming just isn't my thing.

This is complicated to explain so forgive a long post. I'm looking at
overlapping memberships of various groups and have at this stage a
worksheet with two columns of data  to hold the overlap data. Column A
holds the name of a group and adjacent to it in column B will be the
name of an individual member of the group. Column B is sorted on the
individual names of people.

I need to sequentially copy off the group names that an individual
might belong to and paste transpose it elsewhere for further processing.

I imagine that a method might be based on looking down column B until
a change of name occurs and then moving over to column A to read
off/copy the corresponding group names that a person belongs to. I've
tried to get somewhere with that approach and some others but just
can't get my head around it. AJ kindly posted something that looks
like it might have some bearing on this but really I don't know what's
going on.

 
Answer #6    Answered By: Erin Dunn     Answered On: Aug 15

What are you actually doing all this for? Do you have to run any
calculations or whatever?

It occurs to me that if you're only using this for addresses, then outlook
may be a better tool with the possibility of using categories. People can
belong to more than one category. It could also be done in Access with an
SQL query.

Actually, I believe you can use SQL in Excel as well but I've never done
that... Perhaps someone else who has can jump in???

Just a thought...

 
Didn't find what you were looking for? Find more on copying data in various worksheets to one other Or get search suggestion and latest updates.




Tagged: