Excel - efficient way to copy and paste

[srogers]

I have a workbook for each store(5 wkbooks). There is a sheet for each period in the year (13) for each store and each sheet has 4 tables of figures on it (one for each week).

Once a period I will be copying and pasting this information to another workbook. That workbook has 4 sheets, one for each week (week one of all the stores goes on one sheet vertically).

This is the way (see below) I am copying and pasting (it's recorded) one week of info for one store. I'm sure there is a more efficient way. Especially since the sheets I'll be copying from are identical (in that the info that goes in B1 on sheet 1 for store 1 is the same info that goes in B1 on sheet 2 for store 2).
The sheets I am copying to, is the one with 4 sheets, one for each week. Sheet 1 has store 1's data for week 1 and under that store 2's data for week 1, etc.


Windows("202forecast.xls&quot.Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Application.Goto Reference:="Period1Week1_202"
Selection.Copy
Windows("PeriodXXInProcess.xls&quot.Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
Application.Goto Reference:="Week1_202"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

I have the same block as above for Period1Week2 and so on.

If this makes sense, does anyone have an idea of what would make this more efficient? Maybe use of variables, or some type of array?

Thank you -

 

[vb6novice]

To be honest, the sequence of Activate book A, Select a range, Copy, Activate book B, select a range, paste, and Repeat as necessary is actually a very inneficient way to move data from one work book to another, mostly because the Activate makes it look like it's doing something on the screen. What that means is that of the 5 seconds it might seem like it takes to run that block of code, 4 of those seconds is wasted on putting it on the screen. The way to accomplish what you are doing, especially since it is repetitive and lends itself well to arrays, is to set up some loops that move data from one workbook to another workbook without the Activate, Copy, Activate, Paste.

I suggest you set up a series of arrays, one for the 5 store workbooks, one for the 13 periods, and one for the 4 weeks. You'll also need one for the rows and columns for the various weeks (for this you could create a data workbook that identifies the ranges for each week, and the code just gets them from that book).

A set of nested For...Next loops for each of the arrays would work quite well, and very quickly. And all of it could be done while you are looking at the workbook that will be receiving the data, so it looks like it is doing something.

By using a statement such as

Workbooks("Period" & period & "InProcess.xls").Sheets(week).Cells(store_row, column) = Workbooks(store_designator & "Forecast.xls").Sheets(period).Cells(week_row, column)

you could wrap the whole thing up a nice tight piece of code.

The code could open all the necessary work books, prompt you for the week and the period, and then do its thing, close all the store workbooks, and ask you if you want to save it (or just save it without asking).

Your problem has potential written all over it.

 

[SkipVought]

Hi,

You could use a procedure like this.
Assuming that Sheet1 & Sheet2 are the sheets containing our source data..

wsThis = activesheet  ' the target sheet
for each ws in worksheets
  select case ws.name
  case "Sheet1"
    ws.range("Period1Week1_202").Copy _
      Destination:=wsThis.Range("Week1_202")
  case "Sheet2"
    ws.range("Period1Week2_202").Copy _
      Destination:=wsThis.Range("Week2_202")
  end select
next

Not knowing your naming convention, I can't be more specific. But it could be made more general.



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

I missed a few things. You're in several different workbooks. So
wsThis = Workbooks("PeriodXXInProcess.xls&quot.Worksheet("Whatever&quot ' the target sheet
for each wb in workbooks
select case wb.name
case "202forecast.xls".
wb.range("Period1Week1_202&quot.Copy _
Destination:=wsThis.Range("Week1_202&quot
case "Sheet2"
wb.range("Period1Week2_202&quot.Copy _
Destination:=wsThis.Range("Week2_202&quot
end select
next


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[srogers]

I think the case statements may be what I'm looking for. (I'm not so good at arrays yet, although I have an idea that would be really cool.)

Unfortunately the source data for each store is on one sheet. It would be easier if I could include a picture but here is a description of one column (for an example).
(starting week 1's info)
202 (which is the store#)
blank
Sales (column label)
dollar figure (15 or so rows of these)
dollar figure (the total)
3 blank rows
and it starts all over again on the next row for week 2 and then goes on to weeks 3 and 4.

I've already named the source data in each of these areas as you have in the case code (Week1_202).

That's what I'll work on today and Monday and try to post back what worked.
Thank you-

 

[SkipVought]

Send me a copy of your workbooks

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[RoyVidar]

VBA forum and all, just wondering if you've considered linking. It's a bit off topic, but...

Friend of mine works with a lot of economical reporting (month, quarter, year), and did lot's of manual copy/paste (tried to introduce him to recording some VBA, but...).

Now he's reorganized all workbooks using links for the whole company (Head office and 12-15 departments/stores).

To fetch weekly/monthly sums, a formula like:
[tt]='c:\mainfolder\[Store_1.xls]January'!F10[/tt]
does the trick for him.

Just wondering if you've considered if something like this might be applicable for your challenge.

The benefit of this, is to reduce the amount of redundancy (same information stored several places) - which often lead to what's referred to as "multiple versions of the thruth". He changes one number in the original store file, and when the main reporting workbook is opened, it's all updated;-)

Roy-Vidar

 

[srogers]

Thanks for your suggestion Roy. I'll have to get with them and see if there is any type of a timing issue. I'm not sure if it's ok for that file to updated whenever someone else types in their numbers or if the people that use it will need to determine the date.
This is just one of many steps in the whole process so I probably still need to work on learning a good way to update information.
Thanks

 

[srogers]

No, I can't do links. Not unless there was a way to make them dynamic. I need to know which period I'm getting the data from first. I only need 4 weeks of data at a given time but I have to choose 4 weeks from which period.
Back to the case statements...

A question about this line in Skip's code above:
for each wb in workbooks

Which workbooks will the code consider in the collection. The ones that are open? Or the ones that are in the same directory?

Thanks,
Sharon

 

[srogers]

Hi-
Well, at least I got it down to (don't laugh) 40 lines to cover 13 periods (4 weeks each) and 5 stores.

Workbooks("202forecast.xls&quot.Worksheets(PeriodNum).Range("Week1_" & PeriodNum).Copy
Workbooks("PeriodXXInProcess.xls&quot.Worksheets("WEEKONE&quot.Range("Week1_202&quot.PasteSpecial _ Paste:=xlValues

I'm using the PeriodNum variable to capture the period number from the user and naming each week1 the same in each store appended by the period number.

I'm sure I could get it down to 10 lines if I had a loop to go through each week. But it took me long enough to figure out this part so I'll leave that for another day.
Thanks to all.