need to copy data from one workbook to another on selected cells 2

[sanyup2000]

Need to copy data from one workbook to another on selected cells

'I want to create the macro with the following:
'1. Click Run
'2. user gets a prompt to select excel files to be processed
'3. User selects multiple files to be processed
'4. The macro shall copy and paste the data into a destination template and then save the file same as cell B3 in source file. There shall be a separate file created and saved for each source file.
'5. The data that needs to be copied from each worksheet is listed under Run button on Test
'6. The macro shall create a file for each file selected and then save it into a defined path. We can go with C:\.

Test1 - excel macro is stored, Test2 - source excel, Test 3 Destination excel.

'I have included Test1, Test2 and Test3.
'Can anyone please help ? i started working on it, but do not know how to go further as i keep getting error message 'on selecting files. And i deleted the code after that. And i have multiple of these source files that i want to process.

There are whole bunch of other cells that need to be copied. Once i have the base code, i will make those changes myself. I did not mention that huge list. And gave a few cells so as to get a base code. So, it cannot be hard coded. We need code 2 pick it up

i pasted at

http://www.vbaexpress.com/forum/showthread.php?t=15956

, but no response. The test file is located at this link.

 

[SkipVought]

Hi,

What have you done so far?

What specific code or technique are you having a problem with?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sanyup2000]

http://www.vbaexpress.com/forum/showthread.php?t=15956

I will try to be as clear as possible. Hopefully this will clarify and you do not have to go to the posted link.

i have attached the file there. For some reason, i am getting an error message as soon i try to upload the file. Can you please go to this link for me? Or i will try to upload the file using a different computer later on today. I am posting code for now.

There are some particular cells that go from source worksheet to destination worksheets. The destination worksheet is a template. I have copy data from these 100 source worksheets and will create 100 destinations worksheets with a predefined name when the macro is copied to this template.

Option Explicit

Dim mwbMacro As Workbook
Dim mwbInvoice As Workbook
Dim mwbAddressesReport As Workbook
Dim mwbStaticReport As Workbook
Dim mwbLoanActivityReport As Workbook
Dim mwbLoanActivityAdditionalDatesReport As Workbook
Dim maFileName As Variant
Dim mbErrorSwitch As Boolean
Dim miInvoiceCount As Integer
Dim miCounter As Integer
Dim mdStatementDateBegin As Date
Dim mdStatementDateEnd As Date
Dim lsTitle As String
Dim lsFileName As String
Dim lsSavePath As String
Dim lrGSNStartRange As Range
Dim lrAccountsProcessedRange As Range
Dim lrCopyStartRange As Range
Dim lrPasteRange As Range
Dim lrFormulaRange As Range
Dim lrTransactionDetailRange As Range
Dim liBeginningBalance As Double
Dim liEndingBalance As Double
Dim liTransactionCounter As Integer
Dim ldCopyEffectiveDate As Date
Dim ldPasteEffectiveDate As Date

Sub Selectfiletoprocess()

'*****************************************************************************************
'Name: GetUserInput
'Description: Gets list from user of those files to be processed.
'Arguments:
'Author:
'Changes:
'*****************************************************************************************

'Display a common file open dialog to the user to allow
'User to select files to be processed.
maFileName = Application.GetOpenFilename(Title:="Select File(s) to be processed", MultiSelect:=True)
If maFileName = False Then
mbErrorSwitch = True
MsgBox Title:="Error", prompt:="No files chosen. Processing will now terminate."
End If

End Sub

 

[SkipVought]

Rather than telling us HOW you want this done, tell us the WHAT and WHY. What's the business case for this process?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sanyup2000]

This is the business case:

'1. Click Run
'2. user gets a prompt to select excel files to be processed
'3. User selects multiple files to be processed
'4. The macro shall copy and paste the data into a destination template and then save the file with name same as cell B3 in source file. There shall be a separate file created and saved for each source file.
'5. The data that needs to be copied from each worksheet is listed under Run button on Test. For example B3 in source goes to B5 in destination from one worksheet. B7 in another worksheet goes to B9 in the another worksheet.
'6. The macro shall create a file for each file selected and then save it into a defined path. We can go with C:\.

thanks. hope this clarified.

 

[sanyup2000]

Hi "Skip"

I am a beginner to VBA. May be this trouble for you. But, i hope it wonot take a long to write this code. i was thinking of using a Do look to process all the selected input files. So, your help is greatly appreciated.

Thanks

 

[SkipVought]

What you stated is NOT the business case. It is how you expect to solve the business case.

A business case has NOTHING to do with click, select, copy, paste. Those are all METHODS.

Exactly WHAT are you trying to accomplish? An example might be...

I have invoices in separate workbooks in a folder, that I need to summarize according to a user selection criteria, being a given date range.

HOW that requirement is met, is a totally different issue.

So I'm asking again. What is the business case?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sanyup2000]

Hi "Skip"

I get it now. Thanks for clarifying. I have bunch of (about 250) source files. I want to capture data , only selected cells from these source files into the destination template. The template was changed to wat is in the source file. So, we need to create new files with the new updated template. So, I was looking for a macro that can change these source files to destination files using the new template. Capturing the data manually is very tedious. For example: Cell B7 in source file is now B9 in destination. So, we need to capture a whole bunch of those cells. I have to capture data from 2 different worksheets within the source files. And then save the data into 2 worksheets (same name as source file) of destination files. For each source files, there shall be a corresponding destination file created. For example: For 250 source files, there shall be 250 destination files created. The destination file shall be given a name based on entry in Cell B3 of Source File and saved on defined path.
One last thing, May be this looks like design:
Test1---Will store the macro
Test2---Source File (user may or may not select multiple source files). But macro will process them all.
Test3--Template on basis of which cells shall be copied over.
Test4---Destination Files (number of destination files shall be same as source files)
Hope this clarified.

Thanks

 

[SkipVought]

Seems to me that you will need a data map to describe what's moving from one cell location to another.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sanyup2000]

I have the test files there. On test1, i have the data mapping. Disregard the code there as its not working, if u end up going there.
Mapping is done as follows. Source first and then corresponding details for destination.

I have it copied here as well.
Source

Worksheet Type Location
UW Cells A9:BY9
UW Cells CA9:IV9
Valuation Cells B3
Valuation Cells B4
Valuation Cells F52
Valuation Cells H52
Valuation Picture A225

DESTINATION:

Worksheet Type Location
UW Template Cells A7:BY7
UW Template Cells CA7:IV7
Valuation Template Cells B3
Valuation Template Cells B4
Valuation Template Cells F54
Valuation Template Cells H54
Valuation Template Picture A229

 

[sanyup2000]

Hi "Rory"

I am a beginner at VBA. Please let me know:

The code breaks down at " Set wksSource = mwbInvoice.Worksheets(rngCell.Value)" . Get Run Time error 9, subscript out of range. It lets me select the input files and when I select them, I get this run time error. If you have to change mwbInvoice to anything, feel free. I was trying to put code together, it was not working anyways.

For all cells that I need copied and pasted, I can list them on the external macro sheet. (Test1). ????

Thanks for your help and patience.

 

[SkipVought]

Then it should be s simple matter of reading down the source & destination data and performing a copy and paste.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sanyup2000]

There are whole bunch of cells. I just gave a small sample. Rest i will make the changes myself, once i have a base code in place. Thanks
Disregard my previous mail. Its wrong.

 

[sanyup2000]

I meant i have whole bunch of cells that need to be copied and pasted over from 250 some source files. And each files shall have data from 2 worksheets that need to be copied over.

 

[sanyup2000]

Hi Skip

I am a beginner to VBA. So, i am a little lost. Your help shall be greatly appreciated. I will try to post the test files soon on this portal soon.

Thanks

 

[SkipVought]

Turn on your macro recorder and record performing on of the mapping copy and paste operations. That will be a start.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sanyup2000]

Hi Skip

i tried that yesterday before posting here. I got lost when i had to loop through 250 files. Did not know how to take care of that. Got pretty far with the macro recording, but not enough what i wanted to accomplish.

 

[SkipVought]

You just want to record ONE copy and paste or ONE workbook open.

You'll need to post the code that you are haveing trouble with. I'd suggest SMALL code samples.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[sanyup2000]

The user can select one or many files to be processed at one time. It will loop through all of them.


Here is the code:


Option Explicit

Const mc_strTEMPLATE_FILE_PATH As String = "C:\Test2.xls"
Const mc_strSAVE_FILE_PATH As String = "C:\"

Dim mwbMacro As Workbook
Dim mwbInvoice As Workbook
Dim mwbAddressesReport As Workbook
Dim mwbStaticReport As Workbook
Dim mwbLoanActivityReport As Workbook
Dim mwbLoanActivityAdditionalDatesReport As Workbook
Dim maFileName As Variant
Dim mbErrorSwitch As Boolean
Dim miInvoiceCount As Integer
Dim miCounter As Integer
Dim mdStatementDateBegin As Date
Dim mdStatementDateEnd As Date
Dim lsTitle As String
Dim lsFileName As String
Dim lsSavePath As String
Dim lrGSNStartRange As Range
Dim lrAccountsProcessedRange As Range
Dim lrCopyStartRange As Range
Dim lrPasteRange As Range
Dim lrFormulaRange As Range
Dim lrTransactionDetailRange As Range
Dim liBeginningBalance As Double
Dim liEndingBalance As Double
Dim liTransactionCounter As Integer
Dim ldCopyEffectiveDate As Date
Dim ldPasteEffectiveDate As Date

Sub Selectfiletoprocess()

'*****************************************************************************************
'Name: GetUserInput
'Description: Gets list from user of those files to be processed.
'Arguments:
'Author:
'Changes:
'*****************************************************************************************
Dim lngIndex As Long, lngOffset As Long
Dim rngCopy As Range, rngSource As Range, rngDest As Range
Dim wksSource As Worksheet, wksDest As Worksheet
Dim rngCell As Range
Dim shpCopyPic As Shape
Dim strNewPath As String
With Sheet1
Set rngCopy = .Range("A13:A19")
End With
lngOffset = 4
'Display a common file open dialog to the user to allow
'User to select files to be processed.
maFileName = Application.GetOpenFilename(Title:="Select File(s) to be processed", MultiSelect:=True)
If TypeName(maFileName) = "Boolean" Then
mbErrorSwitch = True
MsgBox Title:="Error", prompt:="No files chosen. Processing will now terminate."
Exit Sub
End If
' Loop through selected files
For lngIndex = LBound(maFileName) To UBound(maFileName)
' Open invoice workbook
Set mwbInvoice = Workbooks.Open(maFileName(lngIndex))
' open new template
Set mwbStaticReport = Workbooks.Open(mc_strTEMPLATE_FILE_PATH)
' Get save path
strNewPath = mc_strSAVE_FILE_PATH & mwbInvoice.Sheets(1).Range("B3").Value & ".xls"
' Kill any existing file with the new save name
If Dir(strNewPath) <> "" Then Kill strNewPath
mwbStaticReport.SaveAs Filename:=strNewPath
' Copy data across
For Each rngCell In rngCopy
Set wksSource = mwbInvoice.Worksheets(rngCell.Value)
Set wksDest = mwbStaticReport.Worksheets(rngCell.Offset(0, lngOffset).Value)
Set rngSource = wksSource.Range(rngCell.Offset(0, 2).Value)
Set rngDest = wksDest.Range(rngCell.Offset(0, 2 + lngOffset).Value)
If rngCell.Offset(0, 1).Value = "Cells" Then
rngSource.Copy Destination:=rngDest
ElseIf rngCell.Offset(0, 1).Value = "Picture" Then
For Each shpCopyPic In wksSource.Shapes
Debug.Print shpCopyPic.TopLeftCell.Address
If shpCopyPic.TopLeftCell.Address = rngSource.Address Then
shpCopyPic.Copy
wksDest.Paste Destination:=rngDest
Exit For
End If
Next shpCopyPic
Else
' some other kind of copy!
End If
Next rngCell
With mwbStaticReport
.Save
.Close False
End With
mwbInvoice.Close False
Set mwbInvoice = Nothing
Set mwbStaticReport = Nothing
Next lngIndex

End Sub

 

[SkipVought]

Where is it that you are having problems?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}