How to copy and paste between worksheets in Excel?

[Ronald Mcdonald]

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..

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!

[ketbdnetbp]

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

[ssanfu]

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.

[Ronald Mcdonald]

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)

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

[Ronald Mcdonald]

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....

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

[cplmckenzie]

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

[ssanfu]

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...