Results 1 to 7 of 7

Thread: How to copy and paste between worksheets in Excel?

  1. #1
    Ronald Mcdonald is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2012
    Posts
    16

    How to copy and paste between worksheets in Excel?

    On access, I have a database that prints information into 4 different sheets in excel. HOwever, I want to see if I can copy and paste from one of the sheets below the information of another one. So far, the program copies the data (you can see the dotted range), and selects the range on the other sheet of where to paste. unfortunately, I can't even get anything to paste after that. I feel like I'm so close but I've also tried so many things. as far as code goes, I'm copying from Tactical_Dev and pasting into Tactical. Note I have multiple lines of paste, tried each of em all but no luck..

    Code:
    Set exlSheet = exlBook.Worksheets("Tactical_Dev")
    exlSheet.Activate
    'exlSheet.Application.Goto Reference:="r4C3"
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    exlSheet.Columns("A:A").ColumnWidth = 73
    exlSheet.Columns("B:B").ColumnWidth = 8.5
    exlSheet.Columns("C:C").ColumnWidth = 18.5
    exlSheet.Columns("D:D").ColumnWidth = 12
    exlSheet.Columns("E:E").ColumnWidth = 33
    exlSheet.Columns("F:F").ColumnWidth = 7
    exlSheet.Columns("G:G").ColumnWidth = 16.5
    exlSheet.Columns("H:H").ColumnWidth = 23.5
    exlSheet.rows(1).RowHeight = 25.5
    exlSheet.Range("a1:h1").Font.Bold = True
    exlSheet.Range("a1:k50000").Font.Size = 10
    exlSheet.Range("a1:h1").interior.Color = rgb(192, 192, 192)
    
    'COPYING TEST
    exlSheet.Range("a1:H70").Copy
    Set exlSheet = exlBook.Worksheets("Tactical")
    exlSheet.Activate
    exlSheet.Range("a1:a1").PasteSpecial paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    activeSheet.paste
    activeSheet.Range("a1:a1").PasteSpecial paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Thanks for any help!

  2. #2
    ketbdnetbp is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Location
    Midwest
    Posts
    252
    Ronald Mcdonald -

    If your code works up to this point..
    exlSheet.Range("a1:H70").Copy

    I would try this code after the above line..
    Set exlSheet = exlBook.Worksheets("Tactical")
    exlSheet.Activate
    exlSheet.Range("a1").Select
    ActiveSheet.Paste

    and see if this produces the desired results. If not, post back.

    All the best,

    Jim

  3. #3
    ssanfu is offline VIP
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    2,937
    In Excel, record a new macro (VBA), copying the cell, switching to the second sheet an pasting the data. Stop recording and look at the resultant code.

    I don't use Excel as much as I use to, so I have to resort to this method to remember how program things in Excel.

  4. #4
    Ronald Mcdonald is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2012
    Posts
    16
    No luck with either unfortunately. Still does the same exact thing. I even tried seeing what the VBA in Excel would and same results. (had to put exlsheet. in front of everything, but still no luck)

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+L
    '
        Range("A1:H42").Select
        Selection.Copy
        ActiveWindow.ScrollColumn = 1
        Sheets("Tactical").Select
        Range("A12").Select
        ActiveSheet.Paste
        Range("A10").Select
    End Sub

  5. #5
    Ronald Mcdonald is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2012
    Posts
    16
    Just to follow up, I feel like i've tried EVERYTHING!

    Here's the entire code for sub (most of it is garbage). It seems there is an issue with the paste function. I've tried nearly everything with no luck. I've even tried simplifying things such that I would copy some column and paste it over another in the same sheet....

    Code:
    Private Sub Command128_Click()
    On Error Resume Next
    Dim exlApp As Object 'Excel.Application
    Dim exlBook As Object 'Excel.Workbook
    Dim exlSheet As Object 'Excel.Worksheet
    Dim strProjPath As String
    Dim stDocName As String
    DoCmd.SetWarnings False
    Kill "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls"
    strProjPath = "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC"
    'stDocName = "IssuesLog1.XLS"
    'DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_TOC", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Table_Of_Contents"
    DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_Capital_Test", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Strategic"
    DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_Other_Test", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Tactical"
    DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_Capital_Dev", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Strategic_Dev"
    DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_Other_Dev", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Tactical_Dev"
    'DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_Capital_Test2", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Capital_Test"
    'DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_Other_Test2", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Other_Test"
    'DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_Capital_Dev2", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Capital_Dev"
    'DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_Other_Dev2", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "Other_Dev"
    DoCmd.TransferSpreadsheet acExport, , "Query1_MgtRpt_Projects_KC_All2", "N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls", False, "ALL"
    '" & "AS OF Friday Status Report " & Format(Now(), "short date") & ".xls"
    Set exlApp = CreateObject("Excel.Application")
    Set exlBook = exlApp.Workbooks.Open("N:\EXTRA_CONTROL\ExTRA_Data\OUT_Email\Friday Status Report KC\AS OF Friday Status ReportB.xls")
    'GoTo line999Table_Of_Contents
    'Set exlSheet = exlBook.Worksheets("Table_Of_Contents")
    'exlSheet.Activate
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:L").AutoFit
    Set exlSheet = exlBook.Worksheets("Capital_Test")
    exlSheet.Activate
    exlSheet.Columns("A:L").AutoFit
    'exlSheet.EntireRow.Interior.ColorIndex =
    Set exlSheet = exlBook.Worksheets("Other_Test")
    exlSheet.Activate
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    Set exlSheet = exlBook.Worksheets("Capital_Dev")
    exlSheet.Activate
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    Set exlSheet = exlBook.Worksheets("Other_Dev")
    exlSheet.Activate
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    Set exlSheet = exlBook.Worksheets("ALL")
    exlSheet.Activate
    exlSheet.Cells.rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    Set exlSheet = exlBook.Worksheets("Strategic")
    exlSheet.Activate
    exlSheet.Range("a1:g1").Font.Bold = True
    exlSheet.Range("a1:k50000").Font.Size = 10
    exlSheet.Range("a1:g1").interior.Color = rgb(192, 192, 192)
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    exlSheet.rows(1).RowHeight = 25.5
    Set exlSheet = exlBook.Worksheets("Strategic_Dev")
    exlSheet.Activate
    exlSheet.Range("a1:f1").Font.Bold = True
    exlSheet.Range("a1:k50000").Font.Size = 10
    exlSheet.Range("a1:f1").interior.Color = rgb(192, 192, 192)
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    exlSheet.rows(1).RowHeight = 25.5
    'COPYING TEST
    'exlSheet.Range("a1:H70").Copy
    'Set exlSheet = exlBook.Worksheets("Strategic")
    'exlSheet.Activate
    'exlSheet.Range("a10").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     
    
    Set exlSheet = exlBook.Worksheets("Tactical")
    exlSheet.Activate
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    exlSheet.Range("a1:h1").Font.Bold = True
    exlSheet.Range("a1:k50000").Font.Size = 10
    exlSheet.Range("a1:h1").interior.Color = rgb(192, 192, 192)
    exlSheet.rows(1).RowHeight = 25.5
    
    Set exlSheet = exlBook.Worksheets("Tactical_Dev")
    exlSheet.Activate
    'exlSheet.Application.Goto Reference:="r4C3"
    'exlSheet.Cells.Rows(1).AutoFilter
    exlSheet.Columns("A:Z").AutoFit
    exlSheet.rows(1).RowHeight = 25.5
    exlSheet.Range("a1:h1").Font.Bold = True
    exlSheet.Range("a1:k50000").Font.Size = 10
    exlSheet.Range("a1:h1").interior.Color = rgb(192, 192, 192)
    'Call exlSheet.Range("A1:a2").EntireRow.Insert
    'ActiveCell.FormulaR1C1 = "ZZZZZZZZZZZZZZZZZZ"
    'ActiveCell = "ZZZZZZZZZZZZZZZZZZZZZZZZ"
    
    'COPYING TEST
    exlSheet.Range("a:a").Copy
    'Set exlSheet = exlBook.Worksheets("Tactical")
    'exlSheet.Activate
    exlSheet.Range("b:b").Select
    'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Selection.Paste
    'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'ActiveSheet.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'exlSheet.Range("a10").Select
    'exlSheet.Range("a1:a1").PasteSpecial Paste:=xlValues
    'ActiveSheet.Paste
    'Set exlSheet = exlBook.Worksheets("Tactical")
    'exlSheet.Activate
    'exlSheet.Range("a10").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    ''Set exlSheet = exlBook.Worksheets("Tactical")
    ''exlSheet.Activate
    'exlSheet.Range("a14").Select
    'ActiveSheet.Paste
    ''exlSheet.Range("a10").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     
    
    'exlSheet.Columns("A:Z").AutoFit
    'line999:
    exlApp.Visible = True
    DoCmd.SetWarnings True
    End Sub

  6. #6
    cplmckenzie is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    18
    examples of your work always provides better clarity than words alone ever will.

    without that most answers are a shot in the dark ... at best.

    cplmckenzie

  7. #7
    ssanfu is offline VIP
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    2,937
    Ronald,

    It has been a long time since I have used Access to automate Excel, so I am having to relearn a few things. I am still trying to get the code to execute properly... but since i am having make my own MDB & data and try an understand what you are trying to do in the code, it is taking some time. No offense meant, but the code you posted is a little convoluted (not the correct word, but close).

    I haven't given up yet...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Copy and Paste Row (vb)
    By Computer202 in forum Programming
    Replies: 7
    Last Post: 03-28-2014, 12:59 AM
  2. Copy and Paste Format From Excel (attached)
    By KrenzyRyan in forum Import/Export Data
    Replies: 2
    Last Post: 01-02-2012, 03:56 PM
  3. COPY records from SUBFORM and PASTE to EXCEL?
    By taimysho0 in forum Programming
    Replies: 9
    Last Post: 11-28-2011, 12:45 PM
  4. Copy-Paste
    By BorisGomel in forum Access
    Replies: 4
    Last Post: 10-25-2011, 06:17 AM
  5. Copy/paste to new record.
    By xbox1513 in forum Forms
    Replies: 1
    Last Post: 02-23-2011, 02:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums