0

Hoping someone can help me here..... im new so go easy on me! ;-)

Ive got 2 sheets - 1 "LINK" and 2 "RAW Data"

Now what I want the macro to do is copy data from the "LINK" sheet and put it into the correct columns in the "raw data sheet" - however when the macro is complete, it appears to be putting data in the wrong column (Should start in Column B - Raw Data) - but is replacing data in column A.....Is that because the data Im copying from "LINK" starts in column A?

Is this something to do with it referencing the active window? Is there a way to hard code it so it always checks from rows 4-750 (as data will fluctuate between that many rows).

Please advise

I'm simply turning the macro on and recording a copy, paste special values & formats - here is a clip of beginning of the code it has created.

Range("A5:C5").Select 
Range(Selection, Selection.End(xlDown)).Select 
Selection.Copy 
Sheets("RAW Data").Select 
Range("B4").Select 
Selection.PasteSpecial _ 
    Paste:=xlPasteValuesAndNumberFormats, _
    Operation:= _ xlNone, _
    SkipBlanks:=False, _ 
    Transpose:=False 
Range("E4").Select 
Sheets("LINK").Select
Range("E5:F5").Select
Community
  • 1
  • 1
Hughesy
  • 1
  • 2
  • how are you copying the data? Are you using a `cells()` reference, or a `range()` reference to the new cells? If you a copying entire rows (with `RANGE("E:E")` ), then you will not be able to change the cell position on that row – SeanC Dec 31 '14 at 14:18
  • Im simply turning the macro on and recording a copy, paste special values & formats - here is a clip of beginning of the code it has created Range("A5:C5").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("RAW Data").Select Range("B4").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Range("E4").Select Sheets("LINK").Select Range("E5:F5").Select – Hughesy Dec 31 '14 at 14:54
  • For future reference, you should revise your answer with the code instead of putting it into a comment. – peege Jan 03 '15 at 00:38
  • I'm a little foggy on what you are trying to do specifically. I understand you have two sheets, and you want to copy data from A[row]:C[row] and have the rows go from 4-750. What is the trigger? Could you specify very simply: Column A: is , B: C:, etc, on "Link", and then when (details here) happens Copy to (these columns). One step at a time. The reason I'm confused is because you are referencing A5:C5 and E5:F5. Selecting B4 to paste. Since the problem you stated is that the data is going in the wrong column, I want to be very clear as to what you need. – peege Jan 03 '15 at 00:44
  • Apologies iv probably copied some of the extra code which I wasnt trying to include.... Iv got 2 sheets (LINK) and (RAW) - I want to copy any data that is in LINK (Updated via a linked sheet) into the RAW one, however each week the amount of data will vary between 1 row (row 4) and 744 rows (row 747) as I get a row of data for each hour band, of each day in the year... The first bit of data which I want it to copy is columns A-C, then after that I have data in E-F etc which also needs copying accross – Hughesy Jan 05 '15 at 09:17

1 Answers1

0

I may have misunderstood, but I'm not sure that there is any need for a macro at all.

If all you are trying to do is 'copy' one sheet column into another sheet column, then it is much easier to do so without a macro. For the example columns you've given, simply type ='LINK'!A1 into cell B1 in your "raw data sheet" sheet. You can then drag this vertically/horizontally to the extents that you need copying from the other sheet.

If you're just doing this to learn some VB however, the function you are looking for is called Range. A very good resource for learning some simple VB is http://www.homeandlearn.org/

Hope that helps.

Danny_44
  • 7
  • 2
  • Hi there - yes I need to use a macro as there is loads I need to do with the "LINK" data, chopping bits from it into different sheets etc and I want to automate the process for each month as much as possible. The VBA code shows that Ive used the RANGE function, however it doesnt appear to be working correctly as mentioned in the O.P. – Hughesy Dec 31 '14 at 12:20