route217
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).Off set(0, 31)).Copy rngDst
End With
Set rngDst = wsDst.Range("M" & Rows.Count).End(xlUp).Offs et(0)
wbSrc.Close
'wsDst.Range("M17:AR17").D elete xlShiftUp
End If
strFilename = Dir()
Wend
End Sub
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
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).Off
End With
Set rngDst = wsDst.Range("M" & Rows.Count).End(xlUp).Offs
wbSrc.Close
'wsDst.Range("M17:AR17").D
End If
strFilename = Dir()
Wend
End Sub
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.
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.
ASKER
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
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
ASKER
Hi Brian
I have post on the other question - can we continue here - please...
I have post on the other question - can we continue here - please...
ASKER
Hi Brian
Requirement as per original question and my reply to your first post...
Regards
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.
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.
ASKER
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
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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...
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.
(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.
ASKER
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%
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.
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.
ASKER
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).R ange(strRa nge).Value = wbSrc.Sheets(1).Range(strR ange).Valu e
- 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(
route217,
I'd like to display the values - are you comfortable with the Immediate Pane?
Thanks,
Brian.
I'd like to display the values - are you comfortable with the Immediate Pane?
Thanks,
Brian.
ASKER
Hi Brian
You asking me to display the values from the immediate window....or are u going to...
You asking me to display the values from the immediate window....or are u going to...
ASKER
Hi Brian
Works 100%.....Much appreicated for the excellent feedback.....fantastic...
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
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.
Apologies, crossing posts. What was the cause of the error?
Regards,
Brian.
ASKER
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.)
(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.)
ASKER
appricated Brian....
ASKER
Brian
Hi getting error with code see new posting
https://www.experts-exchange.com/questions/28027059/Run-time-error-script-out-of-range.html
Hi getting error with code see new posting
https://www.experts-exchange.com/questions/28027059/Run-time-error-script-out-of-range.html
ASKER