Link to home
Create AccountLog in
Avatar of route217
route217Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Amend copy and paste range on current macro

Hi Experts

I have the following macro which takes the files from the file path as shown in the vba code and copies and paste the data into the master file (both worksheets) in the two different workbooks have the same name...

I want to amend code so it opens first WBk finds wksht in master file (which is always open) and pastes the data into the following range of cell
1. C23:AA23
2. C25:AA25
3. C30:AA30

The copy and paste range is the same on both wksht...

Close workbook find next WBk and repeat...

Point to mention all workbooks have different names...but the sheets they paste the data into are the say...


Sub Conso()
 
    Dim wbDst As Workbook
    Dim wbSrc As Workbook
    Dim wsSrc As Worksheet
    Dim wsDst As Worksheet
    Dim rngDst As Range
    Dim strFilename As String
    Dim StrPath As String
   
    Application.DisplayAlerts = False
   
           'Update File Path To Suite Requirments - Test Path Only
           
           StrPath = "C:\OutlookAttachments\CMU Attachments\"
           
           Set wbDst = ThisWorkbook
           
           Set wsDst = wbDst.Worksheets("ABC")
               
           Set rngDst = wsDst.Range("M17")
               
                strFilename = Dir(StrPath & "*.xlsm")
               
                While strFilename <> ""
               
                If strFilename <> wbDst.Name Then
               
                    Set wbSrc = Workbooks.Open(StrPath & strFilename, UpdateLinks:=3)
                   
                    Set wsSrc = wbSrc.Worksheets("ABC")
                   
                    With wsSrc
                        .Range("M17", .Range("M" & .Rows.Count).End(xlUp).Offset(0, 31)).Copy rngDst
                    End With
                   
                    Set rngDst = wsDst.Range("M" & Rows.Count).End(xlUp).Offset(0)
                   
                wbSrc.Close
                   
                    'wsDst.Range("M17:AR17").Delete xlShiftUp
              End If
           
            strFilename = Dir()
           
     Wend
 
End Sub
Avatar of route217
route217
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Ps I want to adapt the above vba for one workbook only
Avatar of redmondb
route217,

I want to adapt the above vba for one workbook only
You only want to process the first file that's found in the folder? Regardless of name?

Thanks,
Brian.
Apologies process based on workbook name - if possible...

I have 7 workbooks called:-
1. Abc.xls
2. Zoom.xls
3. Apples.xls

Each workbook has a worksheet that corresponds to a worksheet in the master file...
If the macro already does this then apologies for being stupid the second time...

And copy and paste the various ranges of data...

If you have a better solution happy to listen
Hi, route217,

Please see this post.

Regards,
Brian.
Hi Brian

I have post on the other question - can we continue here - please...
Hi Brian

Requirement as per original question and my reply to your first post...

Regards
route217,

Typically, setting up test data takes a lot longer than actually answering the question, so please post a copy of your master file - by all means delete all the data, we just need the sheets and the headings.

Thanks,
Brian.
Ok Brian

Copy the data in workbook Apples and worksheet abc to workbook master file and worksheets abc....

close workbook then open

workbook zoom and copy the data in worksheet service to master file and worksheet service.....

then close workbook..........
Master-File.xls
Zoom.xls
Apples.xls
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Hi Brian

Firstly thanks for the feedback... The three where for testing only there are 7...

Point 2 error on my part...

Point 4 the correct range is as per original question...

Point 3... Are u saying if a sheets name does not exit then skip and move on...
Thanks,  route217.

(3)  Are u saying if a sheets name does not exit then skip and move on
Currently, if a file is not found or its first sheet doesn't exist in Master then it's skipped. Some possible extra edits...
 - Has a sheet been updated more than once? (i.e. does the same sheet name exist in multiple input files?)
 - Have all sheets in Master been updated? (i.e. is a sheet name missing in the input files?)

Regards,
Brian.
Hi Brian

Just asking - nothing else.....

All workbooks have different name and all worksheets in the workbook are on the master file...

So the coot and paste on looping through each workbook. Should work 100%
route217,

Just clarifying...
 - You want to process all files in the folder - not just the specified ones.
 - You want to process all sheets in the files - not just the first one.

If the macro should be updating all sheets in the Master then I'd suggest also adding the edits that would check for duplicate or missing sheet names. (Otherwise these error situations will happen silently.)

Regards,
Brian.
HI Brian

 - You want to process all files in the folder - not just the specified ones - just specificed ones

 - You want to process all sheets in the files - the files only have one sheet....

Just running the code and getting an run time 1004 error
Application define or object defined error...

wbDst.Sheets(wbSrc.Sheets(1).Name).Range(strRange).Value = wbSrc.Sheets(1).Range(strRange).Value
route217,

I'd like to display the values - are you comfortable with the Immediate Pane?

Thanks,
Brian.
Hi Brian

You asking me to display the values from the immediate window....or are u going to...
Hi Brian

Works 100%.....Much appreicated for the excellent feedback.....fantastic...
route217,

All part of the service - please see attached. For each valid input file, it writes some information to the Immediate Pane. When you get the error just end the run, copy whatever's in the Immediate Pane (make sure you get it all) and paster it here, please.

Thanks,
Brian.Master-File-V3.xls
Thanks, route217.

Apologies, crossing posts. What was the cause of the error?

Regards,
Brian.
error was caused by me....not following instructions.....and incorrectlhy editing the vba you created wrongly....
Thanks,  route217.

(It was supposed to be pretty bullet-proof having or problem with missing files or missing sheets. If you want the edits enhanced, just let me know.)
appricated Brian....