Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need an assist with existing code
Below is code I have attempted to put together to paste data from one
workbook into another. The problem is I need to paste special instead of just pasting. I have attempted to correct this issue but to no success as I am admittedly a VBA amatuer. Any help is appreciated. Sub McoSave() Application.ScreenUpdating = False Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:K2" FromSheet.Range(C1).Copy Workbooks.Open Filename:="F:\Jeff_H\Survey Test\Survey2.xls" Set ToSheet = Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 ToSheet.Paste Destination:=ToSheet.Range("A" & LastRow) Application.Workbooks("Survey2").Close SaveChanges:=True Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need an assist with existing code
Sub McoSave()
Application.ScreenUpdating = False DIM WBto As Workbook Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:K2" SET WBto = Workbooks.Open( Filename:="F:\Jeff_H\Survey Test\Survey2.xls") Set ToSheet = WBto .Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 FromSheet.Range(C1).Copy ToSheet.Range("A" & LastRow).PasteSpecial xlAll WBto.Close SaveChanges:=True Set WBto = Nothing Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False End Sub "hurlbut777" wrote in message ... Below is code I have attempted to put together to paste data from one workbook into another. The problem is I need to paste special instead of just pasting. I have attempted to correct this issue but to no success as I am admittedly a VBA amatuer. Any help is appreciated. Sub McoSave() Application.ScreenUpdating = False Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:K2" FromSheet.Range(C1).Copy Workbooks.Open Filename:="F:\Jeff_H\Survey Test\Survey2.xls" Set ToSheet = Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 ToSheet.Paste Destination:=ToSheet.Range("A" & LastRow) Application.Workbooks("Survey2").Close SaveChanges:=True Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need an assist with existing code
Without looking to deeply at the solution you have provided patrick the one
thing you missed is 6536 should be 65536 in the line LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 This would only cause a problem if there were more than 6536 rows of data so probably not a big deal... but it should be fixed... HTH "Patrick Molloy" wrote: Sub McoSave() Application.ScreenUpdating = False DIM WBto As Workbook Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:K2" SET WBto = Workbooks.Open( Filename:="F:\Jeff_H\Survey Test\Survey2.xls") Set ToSheet = WBto .Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 FromSheet.Range(C1).Copy ToSheet.Range("A" & LastRow).PasteSpecial xlAll WBto.Close SaveChanges:=True Set WBto = Nothing Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False End Sub "hurlbut777" wrote in message ... Below is code I have attempted to put together to paste data from one workbook into another. The problem is I need to paste special instead of just pasting. I have attempted to correct this issue but to no success as I am admittedly a VBA amatuer. Any help is appreciated. Sub McoSave() Application.ScreenUpdating = False Dim FromSheet As Worksheet Dim ToSheet As Worksheet Dim C1 As String Dim LastRow As Long Set FromSheet = ThisWorkbook.Worksheets("results") C1 = "A2:K2" FromSheet.Range(C1).Copy Workbooks.Open Filename:="F:\Jeff_H\Survey Test\Survey2.xls" Set ToSheet = Worksheets("Survey2") LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1 ToSheet.Paste Destination:=ToSheet.Range("A" & LastRow) Application.Workbooks("Survey2").Close SaveChanges:=True Application.ScreenUpdating = True Beep strMB = MsgBox("Survey has been saved. " & _ "Thank you for participating.", vbOKOnly, "Finance Group Survey") Application.ActiveWorkbook.Close SaveChanges:=False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Really need help with existing code. | Excel Discussion (Misc queries) | |||
Help with existing code | Excel Discussion (Misc queries) | |||
need assist with save as csv code | Excel Programming | |||
Help in Modification of existing code | Excel Programming | |||
Code for existing links | Excel Programming |