MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy past issue

  Asked By: Cameron    Date: Aug 18    Category: MS Office    Views: 1253

Sorry but i have another question, one i would imagine is dead easy if
you know (isnt everything)
Anyway i have a list of products in one workbook "Lists_etc" ie

the list goes on for a further 256 products, i then want to copy these
into another workbook, "4 Week data", the problem being that i need
cell b1 to copy into c5, b2 into c14, b3 into c22 b4 into c31 etc.

There must be an easy way of doing this, rather than having to
manually input the data.



3 Answers Found

Answer #1    Answered By: Rickey Scott     Answered On: Aug 18

Can you link from the other workbook? Type = into the destination cell
then go to the products  workbook and click the cell you want. You'll end
up with something like ='[Products.xls]Sheet1'!$B$1. You'll need to do
it for each cell but it could be quicker than cutting and pasting. If
you need values rather than links and the intermediate cells contain
values rather than formulas, you can select the column, copy  and paste
special - values.

Answer #2    Answered By: Monique Perry     Answered On: Aug 18

I can do that, but is there not an easier way. i was wondering if
there was a way of doing it with say a macro in order so speed things
up. The problem is that the first list  (to copy  from) is a list,in
positions A1,A2,A3,A4 etc, but i want to put the values in every 9th
box on the next sheet.Since there are 250 products, i feel i will be
there for ever inputting the data. I was thinking maybe there was a
loop type macro available.

Answer #3    Answered By: Roosevelt Jenkins     Answered On: Aug 18

Here is a macro just for you. Have both workbooks open. Paste the following code
into a VBA module in either of the 2 workbooks. Edit the constants near the top
of the code, to make sure the workbook  names, sheet names, and starting cell
addresses are correct in each workbook. You can run the macro from either
workbook (Tools >> Macro >> Macros, select CopyStuff, click Run).

Public Sub CopyStuff()
Dim CurrRow As Long, ToCol As Integer
Const FromWB = "Lists_Etc.xls" 'source workbook
Const FromSht = "Sheet1" 'source sheet
Const FromCell = "B1" 'first cell of source data
Const ToWB = "4 Week Data.xls" 'destination workbook
Const ToSht = "Sheet1" 'destination sheet
Const ToCell = "C5" 'where FromCell should go

CurrRow& = Range(ToCell).Row
ToCol% = Range(ToCell).Column
Do While Len(ActiveCell.Value) > 0
Application.Workbooks(ToWB).Sheets(ToSht).Cells(CurrRow&, ToCol%).Value
= ActiveCell.Value
CurrRow& = CurrRow& + 9
ActiveCell.Offset(1, 0).Activate
End Sub

Didn't find what you were looking for? Find more on Copy past issue Or get search suggestion and latest updates.