VBA_Macros_Excel

[garryowe]

I am trying to automate a spreadsheet using macros and some VBA. I'm having difficulty pasting the history from windows clipboard and was hoping someone had a solution (VBA).
For instance, I can easily automate copy/paste and clear/empty clipboard. But if there's more than one entry in the clipboard (I need to have around 5 or 6 in the clipboard). How can I program (VBA) to paste the 2nd, 3rd.. history from clipboard?
Thanks for any help you can provide.

 

[Logit]

You will need to post the code you are using. Supplying a small example workbook with just a few entries of data is the best method.

 

[garryowe]

You will need to post the code you are using. Supplying a small example workbook with just a few entries of data is the best method.

Hi Logit
I'm just using some basic coding to paste into excel automatically using a macro.
The issue relates to windows clipboard or Microsoft clipboard; how to reference entries sat in the clipboard. I currently copy from an html (work) source 5 sheets, each day and would like to automate this process.
The only way I think it can be achieved is by copying everything manually (as I am unable to connect/link excel to the html page). I'm currently in discussions with the software company, to see if they can add an extraction option.
However, in the meantime, I was wondering if I could copy everything in one go, I.e. copy page 1, then page 2, then 3... etc. click a button in excel that has the macro attached that will paste all the entries from the clipboard, into the relevant locations in Excel and then clear the clipboard.
Atm, I'm doing each one:

>>> use code - tags <<<

  Sheets("VAST").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets.Add After:=ActiveSheet
    Range("A1").Select
    ActiveSheet.Paste
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").Select

I'm not an expert so there may be a much more efficient method to do this, but afterwards the code takes the raw data and manipulates, using Vlookup's, If and AND etc. functions to display the results of the shift (day shift, night shift).

    Selection.NumberFormat = "m/d/yyyy"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "=IF(AND(RC[-6]>=0.33333333,RC[-6]<=0.770833333333333), ""DS"",""NS"")"

^As an example.
Hope this makes sense and thanks for any help you can offer.

 

[Hany ali]

Where is the excel file? The name of the post must be changed to suit your request

 

[garryowe]

Where is the excel file? The name of the post must be changed to suit your request

Hi Hany ali, not sure why you are asking me to change the name of the post?
I'm using excel and macros, to run VBA code, so why would I change the name?

Why do you need an Excel file (the file is blank, waiting for data to be posted via windows clipboard, using code)? I'm only asking, is there a way to ref. Windows clipboard or Microsoft clipboard entries (that is, windows clipboard can hold up to 100 pasted entries) using VBA code.
I do not know if it is possible, So I cannot create the code that will paste 5 consecutive entries from the clipboard or clear only the first entry and then paste etc. So the code above is about as much as I can offer.
This is why I've posted in this forum.

Hope this makes sense and thanks for any help you can offer.

 

[vletm]

garryowe
# why you are asking me to change the name of the post?
Of course, You've read :

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

How to get the Best Results at Chandoo.org
Use Relevant words in the Title and in the tag Box, This will aid future searches.
How do Your given title match with above?

# Why do you need an Excel file
For the best/fastest results, Upload a Sample File ...
That others could get more clear image about Your used file with some sample data as well as Your code.

 

[Logit]

Sub CpyPste()
    Sheets("VAST").Select
    Range("A1").Select
    ActiveSheet.Paste                       '<<<------What are you pasting ? Nothing has yet been copied.
    Sheets.Add After:=ActiveSheet
    Range("A1").Select
    ActiveSheet.Paste                       '<<<------What are you pasting ? Nothing has yet been copied.
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").Select                   '<<<------Do you really need to select the entire column ?
End Sub

 

[garryowe]

garryowe
# why you are asking me to change the name of the post?
Of course, You've read :

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

How to get the Best Results at Chandoo.org
Use Relevant words in the Title and in the tag Box, This will aid future searches.
How do Your given title match with above?

# Why do you need an Excel file
For the best/fastest results, Upload a Sample File ...
That others could get more clear image about Your used file with some sample data as well as Your code.

Sub CpyPste()
    Sheets("VAST").Select
    Range("A1").Select
    ActiveSheet.Paste                       '<<<------What are you pasting ? Nothing has yet been copied.
    Sheets.Add After:=ActiveSheet
[QUOTE="Logit, post: 290334, member: 38719"]

Hi Logit, thanks for reply.
the data I'm copying is from a html source, that is, it needs to be manually copied into windows or Microsoft clipboard. Then press a button in excel to paste what is in the memory.

Since the clipboards can hold more than one copied object, I was hoping someone new how to reference these objects. That is, I can manually select a cell in excel, open the clipboard and click the object I want to paste (whether this is the first, second... object in the clipboard). Or maybe there is a code that will clear the first entry in the clipboard, so that when the code runs the next Paste command, the second object is pasted into Excel.
Hope this makes sense?

The data I am copying is only available from an HTML source at present. But it will does copy into relevant columns, rows.
I've attached a file showing 1 page of the data from the HTML source (the other pages have different content/columns/rows), but the data is slightly irrelevant, in that I just need to know if there is code to reference objects in the clipboard.

So as an example, if the clipboard objects where referenced as CB1, CB2... etc. Could I use something like:  ActiveSheet.Paste CB2 in the code and paste the 2nd object in the clipboard

Probably not, but just need confirmation

I need to copy 5 pages, go to locations in excel and Paste the objects from windows clipboard.

I will then manipulate the data, by selecting certain columns and formatting them accordingly. 
Columns("H:H").Select                   '<<<------Do you really need to select the entire column ?

I just haven't put the full code in here, because it's just about manipulation afterwards. I.e. I'm just stuck with this copying problem.

 

[vletm]

garryowe
Good that You attached a file.
Where is Your code?
Did You skip 'Title' -part totally?