PDA

View Full Version : Macro not pasting to correct place



pboltonchina
01-17-2009, 06:20 PM
Hi,

I'm pretty new to VBA and have had a lot of help from other sources to get where I am now. My problem is this:-

I have a macro that selects a different part of each worksheet in turn and should paste it to a single sheet called 'List'. The macro is creating the new sheet 'List',selecting and copying the correct area of each sheet, but pasting it over the top of the previous selection, not in the list form I want. All the areas being selected for copying are in the same format, i.e. there is no data in columns A or B, columns C to F contain data, the top cells of columns A & B have a background colour and nothing else. The reason for this format is a column width issue. When the macro pastes to the sheet 'List', I want it to paste the copied columns A to F into Columns A to F on the 'List' sheet, at present it is pasting it into columns E to J. The number of rows of each copied selection varies so I then have to find the last used row to paste the next section of the list.

Thanks for looking, here is my code.

Sub Generate_Repair_Kit_List()
'
'
' Clear or Add a Results sheet
If SheetExists("List") Then
Sheets("List").Activate
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("A1").Select
Else
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "List"
End If
For Each ws In Sheets
ws.Activate
Cells(Rows.Count, "A").End(xlUp).Offset(1).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("List").Activate
Range("C65536").End(xlUp).Select
ActiveSheet.Paste
Next ws
End Sub

Private Function SheetExists(SheetName As String) As Boolean
' Returns TRUE if a sheet exists in the active workbook
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(SheetName)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
End Function

lucas
01-17-2009, 09:37 PM
Range("A65536").End(xlUp).Select
instead of:
Range("C65536").End(xlUp).Select


??

mdmackillop
01-18-2009, 04:47 AM
This should be your tareget to paste to
Sheets("List").Range("C65536").End(xlUp).Offset(, -2)


Avoid Select/Activate to make your code run quicker. Also, when working between sheets, qualify ranges with sheet references to avoid errors and make clearer for future maintenance.

You also need to exclude "List" from your loop.


Option Explicit
Sub Generate_Repair_Kit_List()
'
'
' Clear or Add a Results sheet
Dim ws As Worksheet
If SheetExists("List") Then
Sheets("List").Cells.ClearContents
Else
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "List"
End If
For Each ws In Sheets
If ws.Name <> "List" Then
With ws
Range(.Cells(2, 1), .Range("C65536").End(xlUp).Offset(, 3)).Copy _
Sheets("List").Range("C65536").End(xlUp).Offset(, -2)
End With
End If
Next ws
End Sub