Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
Morning, below you will see the code for when a button is pressed it copies
the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
you're pasteing to the same range that you're copying from and while you're
tried to use objects, you've a few errors. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet WITH rngTemp WsDest.Range("A1").Resize(.Rows.Count).Value = .Value END WITH wbBook.Close True "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
HI Patrick, thanks for your message.
I have tried your code and now it only paste column A and nothing else. "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
Hi Neil
Try this, notice I first paste the entire range, then pasteSpecial column A:B: Sub aaa() Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") Set wbBook = Workbooks.Open _ ("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wsDest.Range("A1", Range("B" & lngRow)).Copy wsDest.Range("A1").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False wbBook.Close True End Sub Regards, Per "Neil Holden" skrev i meddelelsen ... Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
hi Per, thanks for that, now i'm getting an error message Run Time error 1004
method range of object worksheet failed and highlights: wsDest.Range("A1", Range("B" & lngRow)).Copy in yellow. Neil. "Per Jessen" wrote: Hi Neil Try this, notice I first paste the entire range, then pasteSpecial column A:B: Sub aaa() Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") Set wbBook = Workbooks.Open _ ("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wsDest.Range("A1", Range("B" & lngRow)).Copy wsDest.Range("A1").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False wbBook.Close True End Sub Regards, Per "Neil Holden" skrev i meddelelsen ... Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
doesn't work as a function, but it does as a SUB
Option Explicit Sub test() ResolveFormula Range("D6") End Sub Sub ResolveFormula1(rngCell As Range) Dim sCell As String Dim rngPrec As Range Dim index As Long sCell = rngCell.Formula index = 64 For Each rngPrec In rngCell.DirectPrecedents index = index + 1 sCell = Replace(sCell, rngPrec.Address(False, False), Chr(index)) Next msgbox sCell End Sub "Neil Holden" wrote: HI Patrick, thanks for your message. I have tried your code and now it only paste column A and nothing else. "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
please ignore this - i replied to the wrong query, doh ...
"Patrick Molloy" wrote: doesn't work as a function, but it does as a SUB Option Explicit Sub test() ResolveFormula Range("D6") End Sub Sub ResolveFormula1(rngCell As Range) Dim sCell As String Dim rngPrec As Range Dim index As Long sCell = rngCell.Formula index = 64 For Each rngPrec In rngCell.DirectPrecedents index = index + 1 sCell = Replace(sCell, rngPrec.Address(False, False), Chr(index)) Next msgbox sCell End Sub "Neil Holden" wrote: HI Patrick, thanks for your message. I have tried your code and now it only paste column A and nothing else. "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
change
WsDest.Range("A1").Resize(.Rows.Count).Value = .Value to WsDest.Range("A1").Resize( .Rows.Count, .Columns.Count).Value = .Value "Patrick Molloy" wrote: you're pasteing to the same range that you're copying from and while you're tried to use objects, you've a few errors. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet WITH rngTemp WsDest.Range("A1").Resize(.Rows.Count).Value = .Value END WITH wbBook.Close True "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
Replace the last line
<<rngTemp.Copy wsDest.Range("A" & lngRow) with wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
Patrick, that works thanks but i need it to keep adding information, not
overwrite the previous information. Thanks alot. "Patrick Molloy" wrote: change WsDest.Range("A1").Resize(.Rows.Count).Value = .Value to WsDest.Range("A1").Resize( .Rows.Count, .Columns.Count).Value = .Value "Patrick Molloy" wrote: you're pasteing to the same range that you're copying from and while you're tried to use objects, you've a few errors. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet WITH rngTemp WsDest.Range("A1").Resize(.Rows.Count).Value = .Value END WITH wbBook.Close True "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
That works to some extent but for some reason have 76 rows of information and
its pasting 126? Also, it copies to the external excel sheet from the very first row, i need it to start row 2 as i have heading in the external sheet. Sorry to be a pain i'm so close!! "Jacob Skaria" wrote: Replace the last line <<rngTemp.Copy wsDest.Range("A" & lngRow) with wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
From your code it is difficult to understand since you have used
Selection.Copy. What is the selection? Instead refer that as a range object... If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: That works to some extent but for some reason have 76 rows of information and its pasting 126? Also, it copies to the external excel sheet from the very first row, i need it to start row 2 as i have heading in the external sheet. Sorry to be a pain i'm so close!! "Jacob Skaria" wrote: Replace the last line <<rngTemp.Copy wsDest.Range("A" & lngRow) with wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
This is my current code.
Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A13:Q75") Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet With rngTemp wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1 'wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value End With wbBook.Close True "Jacob Skaria" wrote: From your code it is difficult to understand since you have used Selection.Copy. What is the selection? Instead refer that as a range object... If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: That works to some extent but for some reason have 76 rows of information and its pasting 126? Also, it copies to the external excel sheet from the very first row, i need it to start row 2 as i have heading in the external sheet. Sorry to be a pain i'm so close!! "Jacob Skaria" wrote: Replace the last line <<rngTemp.Copy wsDest.Range("A" & lngRow) with wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
'I dont find any issue with the code. The below code will copy rngTemp to
Desitnation worksheet Range("A2") wsDest.Range("A2").Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value 'OR the below code will copy rngTemp to Desitnation worksheet Col A last unused row. lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1 wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: This is my current code. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A13:Q75") Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet With rngTemp wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1 'wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value End With wbBook.Close True "Jacob Skaria" wrote: From your code it is difficult to understand since you have used Selection.Copy. What is the selection? Instead refer that as a range object... If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: That works to some extent but for some reason have 76 rows of information and its pasting 126? Also, it copies to the external excel sheet from the very first row, i need it to start row 2 as i have heading in the external sheet. Sorry to be a pain i'm so close!! "Jacob Skaria" wrote: Replace the last line <<rngTemp.Copy wsDest.Range("A" & lngRow) with wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
One problem with your code is the source and destination sizes are not the
same. Try copying from two different areas on your worksheet and you will see that it won't work the way you are expecting. You can copy from 1 cell to many cells (rows, columns) without any problems or you can select as your source a large area but always chosse as the desination eith one cell, one row, or one column. Never select the source and destination multiple size areas that are different sizes. "Neil Holden" wrote: This is my current code. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A13:Q75") Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet With rngTemp wsDest.Range("A2").Resize(.Rows.Count, .Columns.Count).Value = .Value lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1 'wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value End With wbBook.Close True "Jacob Skaria" wrote: From your code it is difficult to understand since you have used Selection.Copy. What is the selection? Instead refer that as a range object... If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: That works to some extent but for some reason have 76 rows of information and its pasting 126? Also, it copies to the external excel sheet from the very first row, i need it to start row 2 as i have heading in the external sheet. Sorry to be a pain i'm so close!! "Jacob Skaria" wrote: Replace the last line <<rngTemp.Copy wsDest.Range("A" & lngRow) with wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _ rngTemp.Columns.Count) = rngTemp.Value If this post helps click Yes --------------- Jacob Skaria "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
MACRO HELP PLEASE
thats called scope change ;)
so with the original information on the destination sheet, do you want it pushed down ro to the right? add this line: With rngTemp wsDest.Range("A1").Resize(.Rows.Count, .Columns.Count).Insert xlShiftDown wsDest.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value End With "Neil Holden" wrote: Patrick, that works thanks but i need it to keep adding information, not overwrite the previous information. Thanks alot. "Patrick Molloy" wrote: change WsDest.Range("A1").Resize(.Rows.Count).Value = .Value to WsDest.Range("A1").Resize( .Rows.Count, .Columns.Count).Value = .Value "Patrick Molloy" wrote: you're pasteing to the same range that you're copying from and while you're tried to use objects, you've a few errors. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet WITH rngTemp WsDest.Range("A1").Resize(.Rows.Count).Value = .Value END WITH wbBook.Close True "Neil Holden" wrote: Morning, below you will see the code for when a button is pressed it copies the range of the cells into an external excel sheet, however the problem I face now is column A and B need to be pasted as values and its not. Any help on the revised code will be much appreciated. Dim lngRow As Long, rngTemp As Range Dim wbBook As Workbook, wsDest As Worksheet Set rngTemp = ActiveSheet.Range("A12:Q75") 'Range("A12:Q75").Select Selection.Copy Set wbBook = Workbooks.Open("C:\Documents and Settings\neil.holden\Desktop\test2.xls") Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False lngRow = wsDest.Cells(Rows.Count, "B").End(xlUp).Row + 1 rngTemp.Copy wsDest.Range("A" & lngRow) wbBook.Close True |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |