Free Microsoft Excel 2013 Quick Reference

Restrict copy paste ability

I have a protect sheet with only few cells where users can enter data.

Is there anyway to restrict users not to do a copy paste in those areas?

Any help greatly appreciated.

Thanks


Post your answer or comment

comments powered by Disqus
I downloaded the CF+ feature (thank you, thank you, thank you) and created
the formatting for a specific range (e.g. a2:n65). I now want to use the
same options for a66:n300. Is there an easy way to add this new range
without starting over.

2ndly, I lost the ability to copy/paste in this worksheet. Is that normal?

Thank you.
Laura

I am using Excel 2007 and I have a problem in drop down list. My drop down list contains 15 kinds of ticket types. By default, one can't write anything additional in the given drop down list, but he can copy any cell or text and paste in the drop down list. What I actually want is that one can only be able to copy paste the given 15 ticket types and he can't add anything extra. A user has to fill many rows and it would not be easy for him to select manually from drop down for each row, so I want an user to be able to copy any ticket type from those 15 types and paste in the multiple rows simultaneously. But he should not be able to paste or add anything extra for this drop down column.

In short, I want to allow copy paste but there should be a check in the drop down which does not allow to paste data other than the contents of list.

I hope I am able to make my problem understand to everyone. Please help with the solution/macros.

Hi - I have been trying my best to 'lock' down a data-entry sheet for users to input their data. I have Protected the Sheet and unticked the Format cells etc. However, all a user has to do is copy / paste from another spreadsheet and the formatting that I have carefully prepared has been overwritten. Little do they know, what they have done has impact on other areas / formulas.

Therefore, is there a code I can use to simply disable this ability to paste over protected cells?

I am new at this so 'simple' terms would be useful for me.

thanks.

Search List of Values, Highlight, Copy, & Paste

I am looking for help in modifying a macro that I currently have in workbook. Here's what I have:

I have 3 worksheets:
1. List of Search Criteria (List of up to 100 or so countries in column A)
2. Huge Data Set (Sales Data; column K, L, or M will contain country name)
3. Blank Output sheet

I have a search button that will look in the Data Set tab for sales transactions occuring in the list of countries specified on Sheet #1 (column A). The country info could be in any of the 3 rows (K, L, M) on Sheet #2. The macro searches the Data Set with for any matching countries and then copies the row to the 3rd worksheet.

I would like to enhance the search by highlighting the text that was matched (bold red font). Would prefer match to be highlighted after it is copied to the output sheet. But if it's easier to do on the initial Data Set sheet and then copy over that is fine as well.

I looked at other examples but could not find one that has the ability to format (highlight) the match. I would be willing to donate $10USD for any help.

Code:


	VB:
	
 CommandButton1_Click() 
    For i = 2 To Sheets("Countries").Cells(Rows.Count, "A").End(xlUp).Row 
        Set vFound = Sheets("Data Set").Columns("K:M").Cells.Find(What:=Sheets("Countries").Range("A" & i).Value, _ 
        MatchCase:=False) 
        If Not vFound Is Nothing Then 
            vStart = vFound.Address 
            vMatch = True 
            Do 
                Sheets("Data Set").Range("A" & vFound.Row & ":" & "M" & vFound.Row).Copy Destination:= _ 
                Sheets("Output Sheet").Range("A" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1 _ 
                & ":" & "M" & Sheets("Output Sheet").Cells(Rows.Count, "A").End(xlUp).Row + 1) 
                Set vFound = Sheets("Data Set").Columns("K:M").Cells.FindNext(vFound) 
            Loop Until vFound.Address = vStart 
        End If 
    Next i 
    If vMatch  True Then 
        MsgBox ("NO MATCHES FOUND") 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any input?

Thank you!

Hi i have written a code which would help to disable cut copy and paste for a work book. However i kind messed up because now if i want to enable it i cannot figure it out. Can some help please!!! Below is the code.

Module 1


	VB:
	
 
Sub ToggleCutCopyAndPaste(Allow As Boolean) 
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow 
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application 
        Select Case Allow 
        Case Is = False 
            .OnKey "^c", "CutCopyPasteDisabled" 
            .OnKey "^v", "CutCopyPasteDisabled" 
            .OnKey "^x", "CutCopyPasteDisabled" 
            .OnKey "+{DEL}", "CutCopyPasteDisabled" 
            .OnKey "^{INSERT}", "CutCopyPasteDisabled" 
        Case Is = True 
            .OnKey "^c" 
            .OnKey "^v" 
            .OnKey "^x" 
            .OnKey "+{DEL}" 
            .OnKey "^{INSERT}" 
        End Select 
    End With 
End Sub 
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean) 
     'Activate/Deactivate specific menu item
    Dim cBar As CommandBar 
    Dim cBarCtrl As CommandBarControl 
    For Each cBar In Application.CommandBars 
        If cBar.Name  "Clipboard" Then 
            Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True) 
            If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled 
        End If 
    Next 
End Sub 
 
Sub CutCopyPasteDisabled() 
    MsgBox "Sorry!  Cutting, copying and pasting have been disabled in this workbook!" 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This Workbook :


	VB:
	
 
Private Sub Workbook_Activate() 
    Call ToggleCutCopyAndPaste(False) 
End Sub 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    Call ToggleCutCopyAndPaste(True) 
End Sub 
Private Sub Workbook_Deactivate() 
    Call ToggleCutCopyAndPaste(True) 
End Sub 
Private Sub Workbook_Open() 
    Call ToggleCutCopyAndPaste(False) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Using a Macro in Excel 2003, I want to be able to find/copy/paste a particular value, that occurs after the existance of another particular value. All values are in the same column, but there does not need to be a restriction to only look in the one column.

Example: find/copy/paste the word "FindMe" that occurs after the words "ThisOne" on Sheet1 to Sheet2

NotThisOne

FindMe

ThisOne

FindMe

The following code will just find/copy/paste the first existence of "FindMe":


	VB:
	
Sheets("Sheet1").Cells.Find(What:="FindMe", After:=ActiveCell, LookIn:=xlFormulas, _ 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False, SearchFormat:=False).Copy 
Sheets("Sheet2").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,

Jay

I have a spread sheet into which various styles are keyed into several cells...

I have the cells validated to only accept the styles within my list...

however when an external user performs copy paste on my sheet from an external sheet they are able to input invalid data in my validated cells... this messes up my vlookups and other formulas...

is there a way to prevent the user from inputting data via copy/paste?

How can I copy-paste a row of cells containing a name, addresses and etc.,
from Excel to OE with the ability to work with the text once I get it into
OE? I can get it over there, but it seems locked into some format with-out
the ability to work with the text. Is there no way to paste it as
unformatted text so that you can work with it? Thank you very much for any
help here

Jerry

Have defined multiple cells with (varying) length validation rules....as
noted in other threads, data validation is not enforced when user populates
with copy/paste....since copy/paste does trgger "worksheet change" event, how
can I invoke the cells "defined" validation routine from within
"Worksheet_Change"?
My preference is to leverage the cells defined validation settings to avoid
writing individualized subroutines/logic per cell, per worksheet.....

I know using "Worksheet.CircleInvalid" does perform validation of the cells
in defined range, because the invalid ones are "circled".....problem is,
circling cell doesn't "enforce" validation restrictions.

Any/all help is greatly appreciated....

MPR

Good Morning
-I am at the point in my Excel experience of having a workable understanding of Excel. Now its on to Macros & then VBA.
-Index & Match works for me as the purpose underlying the following macro copy & paste excerpt. However it is verbose and totally rudimentary and without the ability to be "volatile" I believe the VBA language calls the routine.
-My attempt is 1.) to open all wkbks and matching or corresponding wksheets in all open wkbks, 2.) copy paste/special/values from lead wkbk/sheet identified Range("A2:C61"). to "Range("P4:R63").Select" in 3 wksheets in multiple subservient wkbks/sheets without all the scrolling & junk that exists in the following partial macro script pasted below.
-Perhaps my biggest weakness is not understanding the routine or language necessary to open all bks and corresponding sheets and corresponding ranges only once for a cross wkbk/sh copy and paste of values.
-Thanks to whomever has the wisdom & patience to address this
-wrpalmer

-Sub priceupdate()
'
' priceupdate Macro
' Macro recorded 8/9/2005 by William Palmer
'
' Keyboard Shortcut: Ctrl+u
'
Workbooks.Open Filename:= _
"C:DataEXCELSTOCKPROFITSIN USE Actual STOCK GAIN-LOSS FORMS BY TaxPayerarp080105.XLS"
Workbooks.Open Filename:= _
"C:DataEXCELSTOCKPROFITSIN USE Actual STOCK GAIN-LOSS FORMS BY TaxPayermep080105.XLS"
Workbooks.Open Filename:= _
"C:DataEXCELSTOCKPROFITSIN USE Actual STOCK GAIN-LOSS FORMS BY TaxPayermsp080105.XLS"
Workbooks.Open Filename:= _
"C:DataEXCELSTOCKPROFITSIN USE Actual STOCK GAIN-LOSS FORMS BY TaxPayersep080105.XLS"
Windows("PRICEUPDATE.xls").Activate
Range("A2:C61").Select
Selection.Copy
Windows("arp080105.XLS").Activate
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Range("P4:R63").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("PRICEUPDATE.xls").Activate
Application.CutCopyMode = False
Selection.Copy
Windows("mep080105.XLS").Activate

Hi, any help with the following would be greatly appreciated:

I have a folder containing 40 single sheet excel workbooks and I would like to automate following tasks:
- Open each excel file (need to open the file so as to update it since it gets the data from another workbook through =formulas)
- Copy paste as values
- Save this as excel html in the same folder as original excel files (keeping the original file name)
- Close (original excel file should not be changed ie formulas should remain in place, only the html file will contain values)
- Since there will always be xHtml files with same name need the macro to replace the excisting file

My abilities with excel are limited to functions, no VBA knowledge other than finding ready codes and pasting them in the module.

Since this routine is to be run almost daily the macro should run all files, instead of one by one.

I just hope that I am not asking too much for excel to handle and I hope that explanation is clear.

George

Windows XP Pro, Office Pro 2003

Note: I wonder whether a solution could be to record a macro for each file and create another macro to run all the others. But then again I need help with the macro to run the other macros.

Hello,

I am looking for a solution to this problem - My rightclick, cut copy and paste is disabled and I cannot drag the cell content. This problem started after I entered the VBE code below.

I desparately need help to get things back as they were. Thanks.

Regards,
Majid

Code:
==============================================

Under 'Thisworkbook'

Option Explicit

Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial

'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow

'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False
.OnKey "^c", "CutCopyPasteDisabled"
.OnKey "^v", "CutCopyPasteDisabled"
.OnKey "^x", "CutCopyPasteDisabled"
.OnKey "+{DEL}", "CutCopyPasteDisabled"
.OnKey "^{INSERT}", "CutCopyPasteDisabled"
Case Is = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
End Select
End With
End Sub

Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
'Activate/Deactivate specific menu item
Dim cBar As CommandBar
Dim cBarCtrl As CommandBarControl
For Each cBar In Application.CommandBars
If cBar.Name <> "Clipboard" Then
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next
End Sub

Sub CutCopyPasteDisabled()
'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub

=====

Under Module

Option Explicit
Const WelcomePage = "Macros"

Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub

Private Sub Workbook_Op()
Call ToggleCutCopyAndPaste(False)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Ensure that the macro instruction sheet is saved as the only
' visible worksheet in the workbook
Dim ws As Worksheet
Dim wsActive As Worksheet
Dim vFilename As Variant
Dim bSaved As Boolean

'Turn off screen flashing
With Application
.EnableEvents = False
.ScreenUpdating = False
End With

'Record active worksheet
Set wsActive = ActiveSheet

'Save workbook directly or prompt for saveas filename
If SaveAsUI = True Then
vFilename = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls*), *.xls*")
If CStr(vFilename) = "False" Then
bSaved = False
Else
'Save the workbook using the supplied filename
Call HideAllSheets
ThisWorkbook.SaveAs vFilename
Application.RecentFiles.Add vFilename
Call ShowAllSheets
bSaved = True
End If
Else
'Save the workbook
Call HideAllSheets
ThisWorkbook.Save
Call ShowAllSheets
bSaved = True
End If

'Restore file to where user was
wsActive.Activate

'Restore screen updates
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

'Set application states appropriately
If bSaved Then
ThisWorkbook.Saved = True
Cancel = True
Else
Cancel = True
End If

End Sub

Private Sub Workbook_Open()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Unhide all worksheets since macros are enabled
Application.ScreenUpdating = False
Call ShowAllSheets
Application.ScreenUpdating = True
ThisWorkbook.Saved = True
End Sub

Private Sub HideAllSheets()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Hide all worksheets except the macro welcome page

Dim ws As Worksheet

Worksheets(WelcomePage).Visible = xlSheetVisible

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
Next ws

Worksheets(WelcomePage).Activate
End Sub

Private Sub ShowAllSheets()
'Author : Ken Puls (www.excelguru.ca)
'Macro Purpose: Show all worksheets except the macro welcome page

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

Hi Experts,

I need to some help with a macro formula. I have Excel 2003 and have set up data validation rules to restrict entries users can make.

However, I noticed that users could still copy/paste over the cells with incorrect data that data validation tool did not pick up. I had a dig around on the net and found a bit of VBA code that could correct the issue. The VBA code is outlined below.

It works perfectly when the workbook is unshared but as soon as I share it the macro ceases to execute giving rise to the same issue as before. Unfortunately I require 20 users to acess the worksheet at various times so I must share it.

Can anyone suggest a way round this problem

Thanks,

Owen

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub

Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

i'm experimenting on disabling copy and pasting but after running the scripts I can't enable the fill handle what seems to trigger the fill handler and can you give me some advice (commands).

Private Sub Workbook_Activate()
       Call ToggleCutCopyAndPaste(False)
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ToggleCutCopyAndPaste(False)
End Sub
 
Private Sub Workbook_Deactivate()
   Call ToggleCutCopyAndPaste(False)
End Sub
 
Private Sub Workbook_Open()
Call EnableMenuItem(Enabled)
    Call ToggleCutCopyAndPaste(False)
End Sub

/***** sub routines ***//
Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
     
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
     
     'Activate/deactivate cut, copy, paste and paste special shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^c", "CutCopyPasteDisabled"
            .OnKey "^v", "CutCopyPasteDisabled"
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
 
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
     'Activate/Deactivate specific menu item
    Dim cBar As CommandBar
    Dim cBarCtrl As CommandBarControl
    For Each cBar In Application.CommandBars
        If cBar.Name <> "Clipboard" Then
            Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
            If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
        End If
    Next
End Sub

thanks in advance

Someone wrote the following code for me because I needed to suspend COPY/PASTE functions.
What this does is completely turn off COPY/PASTE functions for any open workbooks and workbooks I open afterward. I have to reboot my computer to get the features back.

How can I reverse this so that if I open this file again, it just effects this one file? Or what can I do to turn the features back on?

Sub EnableMenuItem(ctlId As Integer,
Enabled As Boolean)
     'Activate/Deactivate specific menu item
    Dim cBar As CommandBar
    Dim cBarCtrl As CommandBarControl
    For Each cBar In Application.CommandBars
        If cBar.Name <> "Clipboard" Then
            Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
            If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
        End If
    Next
End Sub


Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
    Call EnableMenuItem(21, Allow) ' cut
    Call EnableMenuItem(19, Allow) ' copy
    Call EnableMenuItem(22, Allow) ' paste
    Call EnableMenuItem(755, Allow) ' pastespecial
     
     'Activate/deactivate drag and drop ability
    Application.CellDragAndDrop = Allow
     
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
            .OnKey "^c", "CutCopyPasteDisabled"
            .OnKey "^v", "CutCopyPasteDisabled"
            .OnKey "^x", "CutCopyPasteDisabled"
            .OnKey "+{DEL}", "CutCopyPasteDisabled"
            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
            .OnKey "^c"
            .OnKey "^v"
            .OnKey "^x"
            .OnKey "+{DEL}"
            .OnKey "^{INSERT}"
        End Select
    End With
End Sub


Anyone can help with the above subject? Need to restrict certain column only for use of copy/paste function to enforce manual entry.

Hey all,

Basically I would like to copy an area of A1:K500.

A1:K1 contain headings that are also a dropdown list (I'm not sure if its called a dropdown list or not actually) where you can select anything for the colum from the data under it.

When I copy/paste A1:K500 into a new sheet, I do get everything BUT I do not get the ability to use the dropdown list A1:K1. Any ideas how I can do this?

Thanks!

Tom

Ozgrid,

Long-time lurker, first-time poster...

It has been 2 years outside of any real VBA and I'm not having any luck with my usual "chopping" code from forums approach.

Background:
I have three factory sites that provide a forecast of workforce data (supply and demand) by week (x-axis) for every position (y-axis). Note: Workforce is interchangeable and have assigned common categories/positions across sites.

Problem: (Reference attached for worksheets)
Alls well and good until a site's supply and demand don't match, so i'm trying to solve the problem of surpluses and shortfalls in workforce. On the worksheet.hide I have created two sets of tables, one set to display gaps within an individual site's supply and demand, as well as another set of tables to cross reference the origin site's shortfalls against the other site's surpluses (i.e., identify where a sites surplus of workforce can augment another sites shortfall).

First, I am developing a UserForm to request the following input variables: origin site, range to dates (mm/dd/yyyy to mm/dd/yyyy), sites to be cross-referenced, and workforce positions/categories to be looked at.

Next, Based on the inputs given from the origin site (i.e., "what rage of dates do I want to look at", "what sites to I want to cross reference against", and "what positions/categories do I want referenced"), I want to go thru the following logic:

1a) Based on the "Origin" and "Positions" inputs from worksheet.Inputs (e.g., "Site One" and "2", "28", and "46")
1b) Find, Copy, and Paste rows from the origin's table/range ("siteone" range A2:BT75) to "worksheet.Report" based on the condition that that row meets the following positions (i.e., "position" column = 2, 28, or 46)

2a) Based on the "References" and "Positions" inputs from worksheet.Inputs (e.g., "SiteOne:SiteTwo", "SiteOne:SiteThree" and "2", "28", and "46")
2b) Find, Copy, and Paste rows from each of the reference's table/range ("onetotwo" range A228:BT301, etc.) to "worksheet.Report" based on the condition that that row meets the following positions (i.e., "position" column = 2, 28, or 46)

3) Sort the copied rows in worksheet.Report based by position, then copy/paste the table header from worksheet.Hide to worksheet.Report.

4) Delete columns whose headers date ranges fall before the begin date and after the end date provided in worksheet.Inputs

I know there are a lot of variables floating around here but if y'all could help me out with some basic find copy paste coding it'd be greatly appreciated.

Thanks guys!

-tim

Waterfront Manning - Help.xlsm

x post to Excel Forum - http://www.excelforum.com/excel-prog...94#post2844094

Hello,

I have attached my workbook for reference.
I am trying to copy paste cells from my Blank Cutlist worksheet -columns B & C to my Packlist worksheet. The values in the cells on the blank cutlist worksheet are an example but could be greater or smaller in size depending on the size of the job I am working on (i.e. this example populates from rows 4-18 but could be from row 4-500). I need these values to go to the Packlist worksheet row 9-36. I need the description in packlist worksheet( column B) to equal the description in blank cutlist worksheet (column B) and the same for Qty (Blank Cutlist column B / Packlist column A).

The kicker is that I want it to go across the three columns in the Packlist worksheet (columns A&B, E&F, I&J) then down and delete any rows are blank through the description rows until it reaches the next header cell which in my example starts on row 37.

Why can I not upload the file?

Hello,

As the title states, I have multiple workbooks within a folder. Each workbook has a specific worksheet (Named "Data") that I'd like to copy/paste as values into a master file named "Total Financials."

The data in these workbooks are in the same exact format. I'd like to have all the data pasted as values one below the next. So for example the first worksheet would paste from A4:DY61, then the 2nd would paste from A62:DY120.

I'm not strong in macros and this is the only piece to a new process I'm attempting to implement that is holding me back. Any help will be greatly appreciated!

Hi, all. I am newbie for VBA Macro Programming. My task is to create a Excel Macro Programming by copy the wide range of data without open any workbook and paste it in the current workbook. My concept of the programming is i can select the file before proceed for the copy paste event. If no file select, the message box will show up and tell the use no fill selected. Example my data at Book1.xlsm. And i want to copy from range A1 to J20 and paste it at A1 of the Book3.xlsm. But, I now stuck at the middle and having unclear error. The macro can copy and paste it very well. But at the no file select event, it will pop out an error message state that 'False.xlsx' cannot be found. bla bla bla..... Can any pros give me any idea or solution for the problem? Thanks.

My code as below:

	VB:
	
 CopyRanges() 
    Dim wb As Workbook 
    Dim Fname As String 
     
    ChDrive "D" 
    ChDir "D:DocumentsUniMAPInTraTraining" 
    Fname = Application.GetOpenFilename("*,*.xls*", , "Please select file to open") 
     'no file selected
    If Fname = ("*,*.xls*") Then 
        MsgBox "File not selected! Please select a file to open." 
        Exit Sub 
    End If 
    For Each wb In Application.Workbooks 
        If wb.path & "" & wb.Name = Fname Then 
            MsgBox "File " & wb.Name & " is already open" 
             'next line needs fixing - 'Fname' will not activate
             'in case file is open, then just ACTIVATE it
            Exit For 
        End If 
    Next 
     
    If wb Is Nothing Then 
        Set wb = Workbooks.Open(Fname) 
    End If 
    wb.ActiveSheet.Range("A1:J20").Copy 
    ThisWorkbook.ActiveSheet.Range("A1").PasteSpecial Paste:=xlValues 
    Application.DisplayAlerts = False 
     'close 'Fname'
    wb.Close 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello VBA Wizards,

The title pretty much explains it. I currently have a macro (admittedly poorly written) that can copy/paste values and save the results as a .xls file from a single file. However, I need to be able to run a single macro that does the same (copy/paste all values [not formulas] from a .xlsx workbook into a .xls workbook) for all the workbooks (roughly 66 per folder) across many folders (roughly 73) all within the same top-level folder. Any help would be appreciated. Thanks!!

Hello,

I have searched your forum and can't find anything that specifically addresses the two macro challenges that I have.

Challenge 1

I have two sample files attached. Sample 1 shows the initial file, and two a short sample of what I am trying to achieve.

What I need is a macro that will insert two columns to the left of column A, then copy Agent's Name in (D) and the Date in (H) into the new columns. Now the challenge is that not every sequence is the same number of rows. Some have comments, some don't, so I can say to copy say 10 rows and move the the next evaluation. The macro will have to read and identify the original column A cell where is says "Agent Name:" as the breaking point to then pick up and copy & past the name and date in the next evaluation. There is a possibility that the file can be over 500 rows, so I would need this to loop until the macro did not identify the trigger

Challenge 2

This needs to be a separate macro, and not part of the first one. I would like a input box to pop up and ask me the the first 5 to 10 characters of a row that I want to delete. So, if I wanted to delete the row that begins with " NC05 __________ Call Control/Time Management" them I could input 'NC05" into the box and the the macro would loop through the file and delete all instances of this. I know I can do this with a replace, but that still leaves the empty row.

Can someone help me with this? I would greatly appreciate any assistance you can provide to me.

QuestionsForForumSample1
https://docs.google.com/open?id=0B08...d0RfeFB2M21Jdw

QuestionsForForumSample2
https://docs.google.com/open?id=0B08...ZkJ2WTlNTE5yUQ

Thanks

Kathy Smith

Hi,

I am currently doing multiple copy paste tasks from one workbook to another. I would like to make sure that the way I do it is the most efficient.

My source workbook ( where the information from another workbook will be pasted) is subject to changes (the columns might move but will keep the same header). So the first thing I am doing, is creating a module with a sub that search for the source workbook columns headers and the first empty row.


	VB:
	
 
Public sr_name1 As Variant 
Public sr_name2 As Variant 
Public sr_name3 As Variant 
Public sr_first_row As Variant 
 
Public wbA As Workbook, wbB As Workbook 
Public ws1 As Worksheet, ws2 As Worksheet 
 
 
 
Sub define_column_source() 
    Set wbA = ThisWorkbook 
    Set ws1 = wbA.Sheets("Merge") 
    sr_name1 = ws1.Range("a1:cz3").Find(What:="Name 1", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
    sr_name2= ws1.Range("a1:cz3").Find(What:="Name 2", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
    sr_name3 = ws1.Range("a1:cz3").Find(What:="Name 3", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
     
    sr_first_row = ws1.Range("a1:cz3").Find(What:="Name 1", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Row + 1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Then I write another sub in another module where I call the define_column_source sub, open the needed workbook and I copy and paste the information needed to the correct source workbook columns. The other workbook will always have the information starting on the 2nd row.


	VB:
	
 mergefile() 
     
    define_column_source 
     
    Set wbB = Workbooks.Open(ThisWorkbook.Path & "" & "*Workbook1*.xl*", True, True) 
    Set ws2 = wbB.Sheets(1) 
     
    Application.ScreenUpdating = False 
    Application.StatusBar = ("Processing Workbook1...") 
     
    lastrow = ws2.Cells(Rows.Count, "A").End(xlUp).Row 
     
    col_name1 = ws2.Range("a1:cz3").Find(What:="First Name", LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
     
    ws2.Range(Cells(2, col_name1), Cells(lastrow, col_name1)).Copy ws1.Cells(sr_first_row, sr_name1) 
     
    col_name2 = ws2.Range("a1:cz3").Find(What:="Middle Name", LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
     
    ws2.Range(Cells(2, col_name2), Cells(lastrow, col_name2)).Copy ws1.Cells(sr_first_row, sr_name2) 
     
    col_name3 = ws2.Range("a1:cz3").Find(What:="Last Name", LookIn:=xlValues, LookAt:= _ 
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _ 
    , SearchFormat:=False).Column 
     
    ws2.Range(Cells(2, col_name3), Cells(lastrow, col_name3)).Copy ws1.Cells(sr_first_row, sr_name3) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there a more efficient way to do this?

Thank you for your help!


No luck finding an answer? You could always try Google.