VBA Copy and Paste data form one workbook to another.. Problem is data change every week and I need to change column.

[jinnlau]

Hi All I am new to VBA excel, currently I am doing a major project. For this project, every week I will receive new data updates from information company about the percentage of emails we have collected.
My purpose is to create codes so that immediately after I receive the excel from the information counter I can copy the set of data into my master list.

However the problem is that every week the excel column change and I have to only paste 3 specific excels. So how can I make this dynamic? The method I have decided to adopt is to use userform, where the user can specify what column range and row to copy their data from (as shown in the image). However, I do not know how or where to start.

Below is the codes which I have done so far:

Private Sub transferSpecificData()
    Dim percentagecollected As Single
    Dim noofemail As Single
    Dim totalemail As Single
    Dim monthlytransferdata As Workbook
    
    Set monthlytransferdata = Workbooks.Open("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Transfer Monthly Data.xlsb")
    
    Worksheets("Email_AR NTB (3)").Select
    percentagecollected = Range("H:H")
    
    Workbooks.Open ("C:\Users\A9901965\Documents\Jinn\Major Project\VBA Coding\latest\Monthly Tracking Channel Fake.xlsb")
    
    Worksheets("Branch NTB AR").Select
    Worksheets("Branch NTB AR").Range("A1").Select
    
    RowCount = Worksheets("Branch NTB AR").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("branch NTB AT").Range("A1")
    .Offset(RowCount, 0) = percentagecollected
    .Offset(RowCount, 1) = noofemail
    
    End With
    
    monthlytransferdata.Save
    
End Sub

But it gives back error: Run Time error 13: Type mismatch
&
another problem it does not allow me to actually specify which column and row I want to extract my data from.

 

[JazzSP8]

I've similar problems with data coming in different formats.

Is there something you could look for in the reports you need to help identify the data you need is?

I've all kinds of solutions for different types of data I get so it's hard to know what to give as an example.

I guess an easy one is; one report I have has column headers, so I use that to find the actual column name I want. The data I actually want can be anywhere in that column, but it is the only one that comes through as bold text, so once I have the column I look for a cell with bold text and that'll be the one I want.

Another example from the top of my head is that another report I get has an acronym "AGPT" in Column A - I know if I find that string, the data I want is 2 columns across and 2 rows down.

You've obviously got to be careful doing things like that, perhaps build in sense checks and test the theory for a couple of weeks to make sure you're not going to come a cropper. A random occasion of two "AGPT"s in one report say.

You might also consider formatting the imported data so you can get at the things you need more easily.

With another report the information I need is all in bold, so first of all I delete all rows that aren't formatted bold based on a value in Col B, then I delete all rows that aren't a certain value in Col C - Then I loop through and find empty cells in Col A and copy the value from Col D in the row below to the empty cell.

This then gives me data I can use in a VLOOKUP formula.

Just throwing a few things for you to think about

 

[jinnlau]

Hi thanks for your input, however for my data only deals with number so it is not possible to use strings to identify the right column. Moreover the header is only found at say row 25 and it also might change every month. This is why I thought of making it manual i.e. getting the user to manually key in the userform which column and row to extract the data from