+ Reply to Thread
Results 1 to 14 of 14

Problem Moving Data from one Sheet to Another

Hybrid View

  1. 06-11-2014, 09:41 AM #1
    vbaNewbieMan
    vbaNewbieMan is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Problem Moving Data from one Sheet to Another

    Hello ExcelForum,

    I am writing a macro for data transfer from one sheet to another in Excel 2003 and so far this is what I have;

    Sub PMoveData()
' PMoveData Macro
' Macro to move raw data from "P" to PDat
 Sheets("P").Select
    Range("E5:S5").Select
    Selection.Copy
    Sheets("PDat").Select
    Range("C45").Select
    ActiveSheet.Paste
Sheets("P").Select
    Range("E26:S26").Select
    Selection.Copy
    Sheets("PDat").Select
    Range("C46").Select
    ActiveSheet.Paste
Sheets("P").Select
    Range("E47:S47").Select
    Selection.Copy
    Sheets("PDat").Select
    Range("C47").Select
    ActiveSheet.Paste
End Sub
    But when I run that code, it stops at the first 

    Sheets("P").Select
    and tells me that the subscript is out of range.


    If I get rid of the 

    Sheets("P").Select
    it works fine for the first selection as long as I start on the "P" sheet, but messes up the rest.

    Please let me know how to properly select each sheet so I can transfer the data correctly.

    Thanks!!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-11-2014, 09:59 AM #2
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Problem Moving Data from one Sheet to Another 

    Sheets("P").Range("E5:S5").copy detination:=Sheets("PDat").Range("C46")

    not in excel so not tested.
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me
    Reply With QuoteRegister To Reply
  3. 06-11-2014, 10:02 AM #3
    vbaNewbieMan
    vbaNewbieMan is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Problem Moving Data from one Sheet to Another

    Same error occurred "Subscript is out of Range" with that code.
    Reply With QuoteRegister To Reply
  4. 06-11-2014, 10:11 AM #4
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Problem Moving Data from one Sheet to Another

    Have you definately got a sheet called P????
    Reply With QuoteRegister To Reply
  5. 06-11-2014, 10:14 AM #5
    vbaNewbieMan
    vbaNewbieMan is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Problem Moving Data from one Sheet to Another

    Ah there's the problem. It's titled "P", not P. Is there any way to recall that sheet if it's in quotations or do the quotations have to go?
    Reply With QuoteRegister To Reply
  6. 06-11-2014, 10:25 AM #6
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Problem Moving Data from one Sheet to Another

    double them up or use chr(34) & "P" & chr(34)
    Reply With QuoteRegister To Reply
  7. 06-11-2014, 10:32 AM #7
    vbaNewbieMan
    vbaNewbieMan is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Problem Moving Data from one Sheet to Another

    Awesome, Chr(34) worked. Thanks so much!
    Reply With QuoteRegister To Reply
  8. 06-11-2014, 10:34 AM #8
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Problem Moving Data from one Sheet to Another

    no problems.

    Give us some reputation :o)
    Reply With QuoteRegister To Reply
  9. 06-11-2014, 10:40 AM #9
    vbaNewbieMan
    vbaNewbieMan is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Problem Moving Data from one Sheet to Another

    Oh! One more quick question, would it be possible to compile the ranges into one line instead of having a 6 line code for each line?

    Here's the basic code for on line 
    Sheets(Chr(34) & "P" & Chr(34)).Select
    Range("E68:S68").Select
    Selection.Copy
    Sheets("PDat").Select
    Range("C48").Select
    ActiveSheet.Paste
    The next one would be 
    Sheets(Chr(34) & "P" & Chr(34)).Select
    Range("E89:S89").Select
    Selection.Copy
    Sheets("PDat").Select
    Range("C49").Select
    ActiveSheet.Paste
    Reply With QuoteRegister To Reply
  10. 06-11-2014, 10:44 AM #10
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Problem Moving Data from one Sheet to Another

    Yes, using the code i sent.

    Sheets("P").Range("E68:S68").copy detination:=Sheets("PDat").Range("C46")
    or you could do
    Public Sub CopyPaste(rInput  as Excel.range, rOutput as Excel.range)

rnginput.copy destination:=routput


end sub
    not tested as not in excel.

    Then 
    CopyPaste Worksheets("P").range("a1"),Worksheets("Pout").range("b1")
    Reply With QuoteRegister To Reply
  11. 06-11-2014, 10:49 AM #11
    vbaNewbieMan
    vbaNewbieMan is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Problem Moving Data from one Sheet to Another

    Sorry, I should have worded this better. I meant the copying aspect all in one line. As in, I would like to copy all of these ranges that change by 21 rows each time in one line and then paste them into the appropriate spot. There are MANY different things that need to get copied and this could get very huge, very quickly.

    Or is this not possible to copy the various rows because they are not consecutive?
    Last edited by vbaNewbieMan; 06-11-2014 at 10:51 AM.
    Reply With QuoteRegister To Reply
  12. 06-11-2014, 10:58 AM #12
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Problem Moving Data from one Sheet to Another

    Sheets("P").Range("E68:S68").copy detination:=Sheets("PDat").Range("C46")

    same ansa
    Reply With QuoteRegister To Reply
  13. 06-11-2014, 11:06 AM #13
    vbaNewbieMan
    vbaNewbieMan is offline
    Registered User
    Join Date
    06-12-2013
    Location
    Poughkeepsie, NY
    MS-Off Ver
    Excel 2003
    Posts
    36

    Re: Problem Moving Data from one Sheet to Another

    Got it, sorry I had a brain fart and entered it in wrong. Thank you so much for all the help with this. Much obliged.
    Reply With QuoteRegister To Reply
  14. 06-11-2014, 11:10 AM #14
    nathansav
    nathansav is offline
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Problem Moving Data from one Sheet to Another

    No worries, i have brain e-coli sometimes :o)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] problem with moving data to new sheet when row A of new sheet does not have data
    By CityInspector in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-23-2012, 09:16 AM
  2. Cell range data moving from one sheet to another sheet in the same workbook
    By bchdiva in forum Excel General
    Replies: 2
    Last Post: 10-09-2012, 08:40 PM
  3. Moving Data From External Sheet to an internal Working Sheet & Transfers to View Sht
    By bdb1974 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2009, 10:17 AM
  4. Problem moving data between worksheets
    By Timeferret in forum Excel General
    Replies: 3
    Last Post: 02-10-2009, 05:29 AM
  5. [SOLVED] Problem moving data
    By Metrazal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-27-2006, 06:15 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1