Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
Please can someone explain why the following code is pasting to every
sheet in the workbook. I am trying to select a range from one workbook and paste to another creating an unique sheet name on each paste and then listing key info from each sheet into a summary sheet. This seems to work fine until looking at the sheets and realizing that the last pasted info gets pasted to every sheet in the workbook overwriting what was previously there. Here is the code: Sub saveToLib() Application.DisplayAlerts = False Workbooks("Output.xls").Sheets("Summary").Activate Range("B2:N73").Select Selection.Copy 'Select the range to be pasted str = ActiveSheet.Range("K4").Value & ActiveSheet.Range("H4").Text & Left(ActiveSheet.Range("H3"), 10) i = InStr(1, str, ":") str = Left(str, i - 1) & Mid(str, i + 1) Do While InStr(1, str, "/") < 0 i = InStr(1, str, "/") str = Left(str, i - 1) & Mid(str, i + 1) 'Create the unique name for the sheet Loop Call openLib 'A simple sub that opens and activates the ResultLib workbook For Each sht In Workbooks("ResultLib.xls").Sheets If sht.Name = str Then Exit Sub 'If the sheet name is not unique exit Next Sheets.Add ActiveSheet.Name = str ActiveSheet.Range("B2:N73").PasteSpecial 'This should only paste to one sheet, but is currently pasting to all the sheets in the workbook Sheets("Summary").Activate Range("A1").Select i = 1 For Each c In ActiveSheet.Columns(1).Cells If Application.CountA(c.EntireRow) = 0 Then 'Find the first empty row in the sheet and write key info to the summary str1 = "A" & i str2 = "B" & i str3 = "C" & i str4 = "D" & i Range(str1).Value = Sheets(str).Range("K4").Text Range(str2).Value = Left(Sheets(str).Range("H3").Value, 10) Range(str3).Value = Sheets(str).Range("H4").Text Range(str4).Value = Sheets(str).Range("K5").Text 'Workbooks("ResultLib.xls").Close savechanges:=True 'Workbooks("Output.xls").Close savechanges:=True Exit Sub End If i = i + 1 Next Application.DisplayAlerts = True End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Prevent bypassing security by copy/pasting to new workbook | Excel Discussion (Misc queries) | |||
prevent changes to cell references when copying and pasting | Excel Discussion (Misc queries) | |||
Prevent users to use a workbook/sheet after a specified day | Excel Worksheet Functions | |||
Is there a way to prevent pasting data into an Excel worksheet? | Excel Worksheet Functions | |||
prevent Excel from pasting icon when it starts | Excel Discussion (Misc queries) |